Latest topics for the forum "Standard Server"

Error insert

hariszie — GMT

i have problem while insert data in the table.
i have function to copy data between 2 table, like table A to table B. but when i am run this function, data table B not match with table A.
example, i have record in table A with value 1.73 but value will change in table B to 2.00, why? i have used round function to get this record but is not work.

create tablespace fails with a notice of truncation

Mark_Phillips — GMT

I am using a shell script to create a default database. I would like to create a tablespace in the "application support" directory on Mac OS X 10.7.

The script is as follows:

[code]PGDATADIR="$HOME/Library/Application Support/MyCompanyDIR/MyAppName/data"
mkdir "$PGDATADIR"
chown -R postgres:daemon "$PGDATADIR"
chmod -R 700 "$PGDATADIR"
psql postgres -U postgres << EOF
CREATE TABLESPACE "MyAppTS" OWNER master_user LOCATION '$PGDATADIR';
EOF[/code]

This results in an error message in log:
[quote]NOTICE: identifier "/Users/username/Library/Application Support/MyCompanyDIR/MyAppName/data" will be truncated to "/Users/username/Library/Application Support/MyCompanyDIR/MyAppNa"[/quote]

I have not encountered this before. What does it mean? How do I resolve it?

Selecting changed rows via xmin?

davesisk — GMT

I want to identify changed rows in a table(for an ETL feed), and pull a list every 5-15 minutes or so. I don't care whether the operation was insert or update (obviously, a deleted row won't be visible even if it hasn't been vacuumed yet). We can't change the schema of this system, so adding triggers is not really an option. Would it work reliably to do it via the xmin system column? Here's what I have in mind:

1) select max(xmin::text::bigint) from some_table;
2) store it as prior_xmin.
3) sleep for 5 mins
4) select * from some_table where xmin::text::bigint > prior_xmin;

I've tried this manually, and it seems to work well (except the queries aren't exactly fast since xmin isn't indexible...however, I can live with that...it's still quicker than selecting every row from the table). I don't care if I get a few too many rows (they will be MERGE'd/upserted at the target), so as long as I don't miss any changed rows I'm good. As I understand it, xmin will always increment up, until at some point it wraps around. (We'd probably just have to reload all rows into the DW at that point and start the "counter" over...however, I can live with that also...we're reloading EVERY row EVERY night right now, so almost anything is better, even if it isn't perfect.)

We're on 8.3/8.4. Is this a reliable approach, ignoring the eventual wrap-around caveat that I mentioned above?

TIA!
Dave

Where are Postgres 9.1 data folder, and pg_hba.conf files?

glenn_brooks — GMT

I need to edit pg_hda to reset user authentication. The files are no where to be seen on my Win 7 machine! I have exposed hidden files also and have looked in every directly remotely connected to Postgres.

reason for this is that PGadmin corrupted the pg_hda password encription settings when I changed the authentication method from md5 to trusted. so now the server does not start,and I can not get into the DB, or use PGadmin.

None of the postgres data files appear anywhere in my directory structure.

Any help would be greatly appreciated!

Thanks
Glenn P

Problems adding drupal an other web apps to enterpriseDB postgres install

glenn_brooks — GMT

I recently set up an EnterpriseDB Postgres 9.1 install on my Win 7 home 64 bit laptop to do some GIs work. Now I would like to add Drupal and another third party forms editor to configure Postgres with a forms based front end to handle some heavy data creation work. But cant get the apps to install as Apache doesnt recognize them-see below. I have followed the download and unpacked routine to place the app's into a file folder in the Postgres/apache/htdoc folder - where they ( theoretically) should autoinstall.

I can not run the install scripts as my browser doesnt recognize a link to the folder. For example I put //localhost/drupal into the broswer and get only a blank white screen in return. this happens with two different web apps. FYI, I also copied the drupal folder into apache/www folder. wouldnt install their either.

Is there some configuration in the EnterpriseDB routine that does not allow other apps to be installed and run with Postgres??

Please advise how I can fix this. many thanks...

GlennP

Split Strings in a Column to multiple columns – Postgres

Narayana_Y+R+ — GMT

Data is of the format in a column

Column
-----------
a,b,c,d

Output Required is of the form

Column1 Column2 Column3 Column4
------------ ------------- ------------ ------------
a b c d

How can this be achieved?

Function performing function that contains delete logic fail w/o error

Nitewynd — GMT

I'm trying to create a set of functions that remove a group of related records from a set of tables. I've created a "master" function that identified the appropriate keys based on a user input, which then performs subordinate functions which delete the appropriate records from their associated tables. The function runs to completion without errors, but none of the deletes actually do anything. I've included RAISE NOTICE statement throughout the code and can see that the deletes are being executed without throwing any exceptions.

I've tried multiple different approaches to this including putting all the code in a single function with inline for..select..loop logic and nothing seems to work.

One final comment, if I run the "slave" functions individually, everything works correctly.

Following is the "master" function:

CREATE OR REPLACE FUNCTION delete_linkbases(p_linkbase_key numeric)
RETURNS void AS
$BODY$declare
cnt numeric;
w_linkbase_key linkbases.linkbase_key%type := 0;
w_linkbase_link_key linkbase_links.linkbase_link_key%type := 0;
begin
raise notice 'input param: %', p_linkbase_key;

for w_linkbase_key in select linkbase_key
from linkbases
where linkbase_key = p_linkbase_key loop

raise notice 'entering inner loop %', w_linkbase_key;
for w_linkbase_link_key in select linkbase_link_key
from linkbase_links
where linkbase_key = w_linkbase_key loop

raise notice 'running delete_linkbase_arcs %', w_linkbase_link_key;
perform delete_linkbase_arcs(w_linkbase_link_key);

raise notice 'running delete_linkbase_labels %', w_linkbase_link_key;
perform delete_linkbase_labels(w_linkbase_link_key);

raise notice 'running delete_linkbase_locators %', w_linkbase_link_key;
perform delete_linkbase_locators(w_linkbase_link_key);

end loop;
end loop;

raise notice 'delete_linkbases done';
end;$BODY$
LANGUAGE plpgsql VOLATILE STRICT

Following is one of the slave functions:

CREATE OR REPLACE FUNCTION delete_linkbase_arcs(p_value numeric)
RETURNS void AS
$BODY$declare
cnt numeric := 0;
begin
-- raise notice 'entering delete_linkbase_arcs %', p_value;
select count(*) into cnt from linkbase_arcs where linkbase_link_key = p_value;
if cnt > 0 then
delete from only linkbase_arcs where linkbase_link_key = p_value;
raise notice 'linkbase_arcs rows: %', cnt;
else
raise notice 'linkbase_arcs empty';
end if;
end;$BODY$
LANGUAGE plpgsql VOLATILE
raise notice 'delete_linkbases done';
end;$BODY$
LANGUAGE plpgsql VOLATILE STRICT

Unattended install requires EnterpriseDB account-email/password?

xtuple — GMT

I've read that one can embed the Postgres Plus Standard edition in another project's installer but when attempting to test with unattended mode (9.0.2-1 on OSX 10.7) it requires --existingpassword and/or --existingEmail. This wasn't documented for its unattended installer mode and it isn't even well described in the --help menu. Is there a way around this requirement I'm just not seeing, anywhere? Is it expected to use a single account for all users using the installer then?

psql: could not create socket: The requested service provider could not be loaded or initialized.

kolotyluk — GMT

I cannot seem to figure out how to fix this problem - can someone please help :)

The problem happens when I try to run the following command

psql -U postgres -c "select * from pg_stat_activity where datname='hibernate';"

While this works fine in a windows command window, here is the tricky part. I am running this command from a Java application that is running as a Windows service. There is something about the service environment psql does not seem to like, but I cannot figure out what it is. Here is what the command looks like in Java

String [] psql = {"psql", "-U", "postgres", "-h", "127.0.0.1", "-c", "select * from pg_stat_activity where datname='hibernate';"};

The actual results from the process look like

exitValue = 2
outStream =
errStream = psql: could not create socket: The requested service provider could not be loaded or initialized.

(0x0000277A/10106)

Any ideas would be greatly appreciated.

Cheers, Eric

Troubles with Stackbuilder, installing Apache/PHP

Roeland — GMT

Hello everyone,

I am trying to install Apache/PHP with the stackbuilder.
However, when its downloaded and i go to installing i get this error message:

Error trying to install file: Destination ${installdir} resolved to an empty value.

Does anybody know how to resolve this problem?
Thanks in advance
Roeland

query index issue

lwickram — GMT

Hi

The following query takes lot of time to run.
----------------------------------
Query:
select *
from providers_divisions
where registration_date <= to_date('2011-11-04','YYYY-MM-DD')
and registration_date >= to_date('2011-08-04','YYYY-MM-DD')
order by last_name, organisation_name, PracticeLocation, speciality
----------------------------------

When I did an analysis it shows a sort as follows:
"Sort (cost=1713.76..1746.65 rows=13156 width=222)"

However, I've created two indecse: (1) for registration_date; and (2) for the columns: last_name, organisation_name, PracticeLocation, speciality
and set the seqscan to off.

Could you please let me know a way to run this query efficiently.

Thanks.

regards
Leelani

issued from restarting postgres 8.3 in mac server

johnadams — GMT

please kindly to solved the problem?

i have message from error potgresql.log like this
postmaster: the number of buffers (-B) must be at least twice the number of allowed connections (-N) and at least 16
please help me to solve

thanks

Cross-Platform to restore by pg_restore?

erichuang — GMT

Can we use pg_dump to backup windows DB V8.4, then use pg_restore to cross-platform restore to new V9.0 DB on Linux?
or backup windows V9.0 to restore to Linux V9.0?

oid alias

erichuang — GMT

Does anyone know the alias of oid is integer or number?

V9.0 supports Free BSD?

erichuang — GMT

Hi,

Does the Postgres Plus Standard Server/Advanced Server V9.0 support Free BSD platform?

pg_dump with split

David_Hundt — GMT

Running postgres 8.2.6 on a windows xp pc.
My database files are too large to back up with the standard pgadmin gui.

I have read that split command will work for this situation but having trouble with the proper format.
I am using the console command prompt trying different combinations, after reading the postgres online manuals, I keep getting a syntax error.
I'd appreciate your help getting my split to work.

Here is what I last tried.

pg_dump split 1024m -b --host localhost --port 5432 --username "postgres" --format tar --encoding UTF8 --verbose --file "C:\Backup\soapware 2011 test backup\soapware2011Test.backup" "docs_charts"

I get the error - too many command line arguments (first is 1024m)

For comparison, this command copied from pgadmin has acceptable syntax/format

C:\Program Files\pgAdmin III\1.8\pg_dump.exe --host localhost --port 5432 --username "postgres" --format tar --blobs --encoding UTF8 --verbose --file "C:\Backup\soapware 2011 test backup\soapware2011Test.backup" "docs_charts"

but I get the error - pg_dump: [tar archiver] archive member too large for tar format

shapefile loader append option?

bulmer — GMT

Is there any way to use the append (-a) option using the Shapefile Uploader GUI ? Or is it only an option for the shell interface? Thanks. :?:

checking password length

m4dnbri — GMT

Hi,
I've got a problem with checking… in code should be better:
[code]
-- table with checking constraint is incorrect this way…
CREATE TABLE users (
nick varchar(32),
password varchar(32),
primary key(nick),
constraint valid_min_password_length check (length(password) < 6)
);

-- … because:
INSERT INTO users VALUE ('user1', md5('pass'));

-- and new row added.
[/code]
I know the problem: the md5 function creates the hash code and after that engine is checking valid_min_password_lenght.
I have to solve it in SQL and not any other program. Has somebody any idea?

Thanks in advance:
m

stop/start psql 9.1 issue on Mac OSX

Thomas_Schnaus — GMT

Hello,

I can stop the psql 9.1 server with:

postgres$ /Library/PostgreSQL/9.1/bin/pg_ctl stop -l logfile -D /Library/PostgreSQL/9.1/data

But starting the psql server fails:

postgres$ /Library/PostgreSQL/9.1/bin/pg_ctl start -l logfile -D /Library/PostgreSQL/9.1/data
server starting
sh: logfile: Permission denied

Where is the problem and how can I fix that?

*Edit*
I have tried:

postgres$ /Library/PostgreSQL/9.1/bin/pg_ctl -D /Library/PostgreSql/9.1/data start

and that is working!

How to access tables from other database and update them on the same PPSS9.0 server

tpnehete — GMT

Hi,

In one of the migration project I am not allowed to change the database and table design.
Client want it as it is.

In one of the procedure, I want to access some tables from other database and do some DML operations on them.
Both databases are on same PPSS9.0 server.

Through dblink we can access the data from table of other database but can not do any DML on it.

So can anyone suggest a solution for above problem. Do we have any patch for databaselink?

How to create synonym

tpnehete — GMT

To create SYNONYM in PPSS 9.0,
do we need a patch or to run a script?

return error code(sqlcode or sqlstate) from function with return type int

tpnehete — GMT

Hi,
while migration from Oracle or any other RDBMS, we need to return error code from exception block.
Like,
create or replace function upd_sal(empid int) returns int
as
begin
update emp set sal =sal+(sal*.1) where deptno =10;
return 0;
exception when others then
return sqlcode;
end;
how to convert this function in plpgsql? Since plpgsql support only sqlstate which of char datatype
So how we can return char datatype error code from function with int return type.
Is there any way to return int error code like sqlcode from plpgsql stored function?

how to create database link in postgres plus standard server 9.0

tpnehete — GMT

Hi,
how to create db link in PPSS9.0?
I want to access some tables from other database on PPSS9.0 in function.

Can we use sqlcode in functions of plpgsql

tpnehete — GMT

Hi,
For backward compatibility and some other reasons we want to use
SQLCODE in our functions of PLPGSQL.
But in Postgres Plus Standard Server 9.0 it gives error for SQLCODE.
Is there any way to use SQLCODE in functions of PLPGSQL at the place of SQLSTATE?
Also what is the datatype of SQLSTATE?

Which datatype we should use to store exactly DD-MM-YYYY HH12:MI:SS value

tpnehete — GMT

Which data type we should use to store exactly 'DD-MM-YYYY HH:MI:SS' FORMAT DATE AND TIME.
When we use timestamp it stores values above the seconds like 11:30:44.23432 which we don't want.