[GENERAL] Insert/select union bug
create table temp(a timestamptz); insert into temp(a) select NULL; /* this passes */ insert into temp(a) select NULL union select NULL; /* fails: ERROR: column "a" is of type timestamp with time zone but expression is of type text */ I need to insert hundreds of thousands of rows, and insert into ... select union is by far more efficient than multitude of inserts. Postgres 8.1.3, FreeBSD. Has this (maybe) been fixed in most recent release? Peter ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Insert/select union bug
On Wed, Sep 27, 2006 at 12:14:44PM +0300, Peter wrote: > create table temp(a timestamptz); > > insert into temp(a) select NULL; /* this passes */ > > insert into temp(a) select NULL union select NULL; /* fails: > ERROR: column "a" is of type timestamp with time zone but expression is > of type text > */ Perhaps you could indicate in the subselects the type? For example: insert into temp(a) select NULL::timestamptz union select NULL; I think as long as the first has the right type, you're set. BTW, UNION ALL is probably more efficient. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Insert/select union bug
Martijn van Oosterhout wrote: On Wed, Sep 27, 2006 at 12:14:44PM +0300, Peter wrote: create table temp(a timestamptz); insert into temp(a) select NULL; /* this passes */ insert into temp(a) select NULL union select NULL; /* fails: ERROR: column "a" is of type timestamp with time zone but _expression_ is of type text */ Perhaps you could indicate in the subselects the type? For example: insert into temp(a) select NULL::timestamptz union select NULL; I think as long as the first has the right type, you're set. BTW, UNION ALL is probably more efficient. Have a nice day, UNION ALL would probably be quicker still, you're right. Typecast eliminates the issue, you're right on that as well. However, my problem is that those statements are dynamically generated on various tables/columns, so typecasting would mean extracting target field type and translating fieldtype code into SQL typename. Rather messy. Peter
Re: [GENERAL] Restart after power outage: createdb
Jim Nasby wrote: [EMAIL PROTECTED] ~]$ psql bar psql: FATAL: database "bar" does not exist [EMAIL PROTECTED] ~]$ createdb bar createdb: database creation failed: ERROR: could not create directory "base/65536": File exists [EMAIL PROTECTED] ~]$ createdb bar Could this be a timing issue? IE: are you just issuing the first createdb bar too soon after the database has started? I suspect this is a bug... Probably not too soon, since postgresql is started by init.d during boot and it is probably a minute or so after that before I can get logged in, open a shell, issue the commands, etc. -- -**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*--- Jon Lapham <[EMAIL PROTECTED]>Rio de Janeiro, Brasil Personal: http://www.jandr.org/ ***-*--**---***--- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Insert/select union bug
On Wed, Sep 27, 2006 at 01:05:56PM +0300, Peter wrote: > Typecast eliminates the issue, you're right on that as well. However, my > problem is that those statements are dynamically generated on various > tables/columns, so typecasting would mean extracting target field type > and translating fieldtype code into SQL typename. Rather messy. In general, pushing down of types from the insert is a bit tricky, the planner tries to unify the UNION first and needs a type for the values. It guesses "text" if it doesn't know. Using COPY avoids this issue ofcourse, because there's a direct link to the table. Similarly, as of 8.2 it will be possible to do: INSERT INTO table (blah) VALUES (x,y,a),(f,d,g),(s,f,g), etc... Which will also avoid the issue. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] postgresql ddl scripts - drop object fails entire script
[EMAIL PROTECTED] wrote: On the one hand I like how the schema scripts fail when there is a single problem with a DDL statement. On the other hand sometimes it is a pain - especially to take out all the 'drop sequence', 'drop table' etc commands when creating a new database. Until 8.2 is available, maybe wrapping your DDL statements in a pl/pgsql SP will work. pl/pgsql has exceptions... I haven't tried this, but I expect it will work. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] pl/pgsql NEW variable substitution
i want to check in a trigger if certain columns are not left empty. The columns i have to check are stored in another table. How do i do the following BEGIN SELECT INTO col_record * FROM modules WHERE type_module_id = NEW.type_module_id AND is_afsluit_kolom; IF NOT FOUND THEN RAISE EXCEPTION 'geen afsluitkolom gedefinieerd voor type_module % ', NEW.type_module_id; ELSE col_naam := col_record.kolom_naam; RAISE NOTICE 'kolom to check is % ', col_naam; --- check if afsluitrecord is empty IF NEW.col_naam != '' THEN RETURN NEW; ELSE RAISE EXCEPTION 'dit record is afgesloten voor wijzigingen !'; END IF; END IF; END; I want col_naam to be evaluated before used with NEW . Now i get a warning that the table the trigger is written for doesn't has a column 'col_naam'. jef peeraer ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
reply-to address broken (Was: Re: [GENERAL] postgresql ddl scripts - drop object fails entire script)
[EMAIL PROTECTED] wrote: On the one hand I like how the schema scripts fail when there is a single problem with a DDL statement. Your mail address bounces. Unfortunately my Trash is broken, so I can't show you the error; I was a bit quick deleting it. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Strange query results with invalid multibyte character
Hi, I have a strange problem. I have 2 database, both created with: CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default; Running the queries below on the first database is OK and do what I expect. If I create a backup of the first datase and restore that backup in the second database the following happens: The first query (see below) returns 17 records, all containing 'Boonk%'. The second query (see below)returns 'ERROR: invalid multibyte character for locale' Query 1: select lastname from salesordercustomer where lower(lastname) like 'boonk%' Query 2: select lastname from salesordercustomer where upper(lastname) like 'BOONK%' Both database are running *exactly* the same version of Debian Etch AMD64 and *exactly* the same version of PostgreSQL (8.1.4 for AMD64) Can anyone explain this to me? TIA Joost ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Transaction is read-only in auto commit mode
Thanks Jim. I dowloaded the latest version of the JDBC drivers and installed them on the affected sites. So far, there were no error reported in the last12 hours. Hope it works! Thanks again. AsokJim Nasby <[EMAIL PROTECTED]> wrote: On Sep 26, 2006, at 3:26 PM, Asok Chattopadhyay wrote:> My application based on Java servlets was running fine with version > PostgreSQL 7.x, but started giving error: "transaction is read- > only", in version 8.0 and 8.1. I am using Suse Linux 9.3/PostgreSQL > 8.0 or Suse Linux 10.1/PostgreSQL 8.1. I am using JDBC 3 drivers > and all connections are in auto-commit mode. Could you please tell > me what's going wrong. Strangely, I looked through all the postings > in all the forums but could not find a mention of this problem. Am > I doing something exttremely stupid or has something changed in > version 8 onwards that's causing this problem? The error appears > sporadically, not always but quite frequently. I am using the > standard postgresql.conf, except that I had increased the shared > buffers and working memory sizes.Something is creating transactions in a read-only mode, though it's not clear why that would change from 7.4 to 8.0 (or were you using something even older?) I suspect it's an abstraction layer trying to be cute and getting things wrong, but that's just a guess. Turning on query logging should provide insight.--Jim Nasby [EMAIL PROTECTED]EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Re: [GENERAL] Insert/select union bug
Typecast eliminates the issue, you're right on that as well. However, my problem is that those statements are dynamically generated on various tables/columns, so typecasting would mean extracting target field type and translating fieldtype code into SQL typename. Rather messy. In general, pushing down of types from the insert is a bit tricky, the planner tries to unify the UNION first and needs a type for the values. It guesses "text" if it doesn't know. OK, that explains it. Using COPY avoids this issue ofcourse, because there's a direct link to the table. Similarly, as of 8.2 it will be possible to do: INSERT INTO table (blah) VALUES (x,y,a),(f,d,g),(s,f,g), etc... Which will also avoid the issue. COPY wont work... my list of columns for insert is also dynamically built and will never cover all fields in table. Multiple comma-separated values lists will also work... but I dont think I have time to wait for 8.2... thanks for all the tips! Peter
Re: [GENERAL] Insert/select union bug
On Sep 27, 2006, at 20:39 , Peter wrote: Using COPY avoids this issue ofcourse, because there's a direct link to the table. Similarly, as of 8.2 it will be possible to do: INSERT INTO table (blah) VALUES (x,y,a),(f,d,g),(s,f,g), etc... Which will also avoid the issue. COPY wont work... my list of columns for insert is also dynamically built and will never cover all fields in table. How about doing the two-step? Create a temp table with the columns you're going to load, COPY into the temp table, and do a INSERT INTO table (...) SELECT ... FROM temp_table? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Insert/select union bug
On Wed, Sep 27, 2006 at 02:39:13PM +0300, Peter wrote: > COPY wont work... my list of columns for insert is also dynamically > built and will never cover all fields in table. You don't have to include all columns for copy, just the fields you copy into, the rest should get the default. COPY table (fields) FROM STDIN; The only restriction is that you can't use expressions. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Documenting stored procedures and functions
On Sep 26, 2006, at 11:14 PM, Jorge Godoy wrote: I dunno if this is the best list to ask about it, but it sounded "general" enough to me :-) Sorry if I'm on the wrong place. I'd like to know how you're documenting your functions and stored procedures, including their usage, input and output types, description, updates/versioning, etc. I'm starting to create some internal standard to do that and it there are any tools -- preferably multiplatform, including specially Windows and Linux -- to help with that I'd greatly appreciate any recommendations. Take a look at http://pgfoundry.org/projects/autodoc/. I believe it uses comments (ie: COMMENT ON) as well, so you can get some info into that. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Restart after power outage: createdb
On Sep 27, 2006, at 6:24 AM, Jon Lapham wrote: Jim Nasby wrote: [EMAIL PROTECTED] ~]$ psql bar psql: FATAL: database "bar" does not exist [EMAIL PROTECTED] ~]$ createdb bar createdb: database creation failed: ERROR: could not create directory "base/65536": File exists [EMAIL PROTECTED] ~]$ createdb bar Could this be a timing issue? IE: are you just issuing the first createdb bar too soon after the database has started? I suspect this is a bug... Probably not too soon, since postgresql is started by init.d during boot and it is probably a minute or so after that before I can get logged in, open a shell, issue the commands, etc. My brain is foggy on exactly what rules are used to clean $PGDATA up after a crash, but I'm pretty sure it's not supposed to work like that. If you can re-test with 8.2RC1 and still reproduce it (please watch the logs to make sure crash recovery is complete before you try the createdb), I'd ask on pgsql-bugs. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pl/pgsql NEW variable substitution
On Sep 27, 2006, at 6:16 AM, jef peeraer wrote: i want to check in a trigger if certain columns are not left empty. The columns i have to check are stored in another table. How do i do the following BEGIN SELECT INTO col_record * FROM modules WHERE type_module_id = NEW.type_module_id AND is_afsluit_kolom; IF NOT FOUND THEN RAISE EXCEPTION 'geen afsluitkolom gedefinieerd voor type_module % ', NEW.type_module_id; ELSE col_naam := col_record.kolom_naam; RAISE NOTICE 'kolom to check is % ', col_naam; --- check if afsluitrecord is empty IF NEW.col_naam != '' THEN RETURN NEW; ELSE RAISE EXCEPTION 'dit record is afgesloten voor wijzigingen !'; END IF; END IF; END; I want col_naam to be evaluated before used with NEW . Now i get a warning that the table the trigger is written for doesn't has a column 'col_naam'. Try http://www.postgresql.org/docs/8.1/interactive/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN, though I'm not sure if it'll work with NEW. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] postgress 8.1.4 deadlocking??
Hi, I fell into the following problem (unfortunately, the database contents has sensitive customer information, so can publish very little of that). Currently postgress process takes close to 100% CPU time. I've restarted the process a moment ago, and it was calm for a brief minute. It started to consume the CPU once I've issued: ALTER GROUP majster DROP USER rafal; ... which took close to 5minutes to complete. Then I've issued: VACUUM ANALYZE ; ... which I had to cancel when it didnt' completed within another 10minutes. CPU utilisation remains at 100% even after the VACCUM was canceled. pg_dumpall was successfull during the large-CPU-usage time. ...another postgress-process restart, and another VACCUM ANALYSE, this one completes in 3-5 secs. Now "ALTER GROUP ..." goes OK. And everything seams working. the database is currently almost empty: c.a. 100k records within c.a. 20 tables with another 30 or so views. There are c.a. 6k userids (roles) created in the cluster. Any ideas where should I look (system tables? process stats??) to dieagnose the problem? just to be prepared when it appears again? This is posgresql 8.1.4 on linux-debian. -- -R ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Strange query results with invalid multibyte
Hi Joost. (hoe gaat het ermee?) I would like to test this too. Could you please provide the data you are inserting into the database. Regards, Gevik On Wed, 2006-09-27 at 13:13 +0200, Joost Kraaijeveld wrote: > Hi, > > I have a strange problem. > > I have 2 database, both created with: > CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = > pg_default; > > Running the queries below on the first database is OK and do what I expect. > > If I create a backup of the first datase and restore that backup in the > second database the following happens: > The first query (see below) returns 17 records, all containing 'Boonk%'. > The second query (see below)returns 'ERROR: invalid multibyte character for > locale' > > Query 1: > select lastname from salesordercustomer where lower(lastname) like 'boonk%' > > Query 2: > select lastname from salesordercustomer where upper(lastname) like 'BOONK%' > > Both database are running *exactly* the same version of Debian Etch AMD64 and > *exactly* the same version of PostgreSQL (8.1.4 for AMD64) > > Can anyone explain this to me? > > TIA > > Joost > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > -- Regards, Gevik Babakhani ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Sockets and posgtres
On Sep 26, 2006, at 3:25 PM, J S B wrote: Hi, I'm trying to make my database a client for an external unix based deamon process acting as a server. I was thinking of writing some clinet application in a shared object in the database (same as what we do with socket programing) that does other Db related activities as well. Would be a right thing to do or we have specific tools available in postgres to accomplish such kind of things. If I'm understanding what you're trying to do (have a function connect to an external process?), I don't think anything exists right now. But it shouldn't be too hard to write something to do that. You might want to create a generic utility and put it on pgFoundry in case others find it useful. Oracle has a package that allows for TCP (as well as other communications) from the database; looking at their interface might be useful. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Fulltext index in postgres?
I havet to develop an search engine over a postgres table. I know in mysql the fulltext index to do it more quicky than using like % ...There is any like t in postgres?Thanks
Re: [GENERAL] Strange query results with invalid multibyte character
"Joost Kraaijeveld" <[EMAIL PROTECTED]> writes: > I have 2 database, both created with: > CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = > pg_default; But have they got the same locale settings (lc_collate, lc_ctype)? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] File System Access:
All, I have a need to create/copy/delete files on my server from a function in PostreSql. I am running 8.x PostreSql on Windows 200x and my functions etc are currently only in PL/pgSQL. Is there a way to create files/copy them and delete them from PL/pgSQL or could i call an external bat file job from PL/pgSQL. Or do I need to branch into doing something with a different language (perhaps PL/PerlU)? If anyone has an example or experience they could share I would appreciate it. I had thought about having a table with a list of operations and then scheduling a job in the OS to call and execute them but these operations need to happen in real time. Thanks in advance, Oisin smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Fulltext index in postgres?
Title: Nachricht Hi, try contrib/tsearch2 http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ HTH Hakan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of HHDirecto.NetSent: Wednesday, September 27, 2006 4:56 PMTo: pgsql-general@postgresql.orgSubject: [GENERAL] Fulltext index in postgres?I havet to develop an search engine over a postgres table. I know in mysql the fulltext index to do it more quicky than using like % ...There is any like t in postgres?Thanks
Re: [GENERAL] File System Access:
On Wed, Sep 27, 2006 at 11:15:19AM -0400, Oisin Glynn wrote: > All, > > I have a need to create/copy/delete files on my server from a function > in PostreSql. I am running 8.x PostreSql on Windows 200x and my > functions etc are currently only in PL/pgSQL. Is there a way to create > files/copy them and delete them from PL/pgSQL or could i call an > external bat file job from PL/pgSQL. Nope, for that you need an untrusted language like pl/perlu. > I had thought about having a table with a list of operations and then > scheduling a job in the OS to call and execute them but these operations > need to happen in real time. This is a better approach. Have a daemon that permanently attaches to the database and executes a LISTEN. Then when someone else adds a row to the table, it executes a NOTIFY, the daemon will get told and can do the work. LISTEN/NOTIFY is pretty much instantaneous, and you have a backup plan (if copy/create/update fails for some reason, leave the row in the table and try again later). Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Strange query results with invalid multibyte
Hi Tom, On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote: > "Joost Kraaijeveld" <[EMAIL PROTECTED]> writes: > > I have 2 database, both created with: > > CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE > > = pg_default; > > But have they got the same locale settings (lc_collate, lc_ctype)? According to the postgresql.conf of the *working* database the locales are: lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' According to the other obe: lc_messages = 'en_US' lc_monetary = 'en_US' lc_numeric = 'en_US' lc_time = 'en_US' Could this be the difference? Is there any documentation available somewhere, on how these locale settings work and interact (in combination with the server and/or client platform???), besides the explanation in the PostgreSQL manual (http://www.postgresql.org/docs/8.1/interactive/charset.html#LOCALE) (which is to terse for me to understand)? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Strange query results with invalid multibyte character
Joost Kraaijeveld <[EMAIL PROTECTED]> writes: > On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote: >> But have they got the same locale settings (lc_collate, lc_ctype)? > According to the postgresql.conf of the *working* database the locales > are: > lc_messages = 'C' > lc_monetary = 'C' > lc_numeric = 'C' > lc_time = 'C' > According to the other obe: > lc_messages = 'en_US' > lc_monetary = 'en_US' > lc_numeric = 'en_US' > lc_time = 'en_US' You did not show us the settings that actually count, but based on this I'm guessing they are en_US also. What you need to find out next is what character set encoding that locale implies on your machine. I'm betting it's not utf8 though :-( regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Strange query results with invalid multibyte
On Wed, 2006-09-27 at 12:10 -0400, Tom Lane wrote: > Joost Kraaijeveld <[EMAIL PROTECTED]> writes: > > On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote: > >> But have they got the same locale settings (lc_collate, lc_ctype)? > > > According to the postgresql.conf of the *working* database the locales > > are: > > lc_messages = 'C' > > lc_monetary = 'C' > > lc_numeric = 'C' > > lc_time = 'C' > > > According to the other obe: > > lc_messages = 'en_US' > > lc_monetary = 'en_US' > > lc_numeric = 'en_US' > > lc_time = 'en_US' > > You did not show us the settings that actually count, but based on this > I'm guessing they are en_US also Ah, sorry: "show lc_collate" and "show lc_ctype" confirm your guess. > What you need to find out next is > what character set encoding that locale implies on your machine. I'm > betting it's not utf8 though :-( I am not sure what you mean by that but maybe this helps: the output of "locale" on the working machine is: LANG= LANGUAGE=en_US:en_GB LC_CTYPE="POSIX" LC_NUMERIC="POSIX" LC_TIME="POSIX" LC_COLLATE="POSIX" LC_MONETARY="POSIX" LC_MESSAGES="POSIX" LC_PAPER="POSIX" LC_NAME="POSIX" LC_ADDRESS="POSIX" LC_TELEPHONE="POSIX" LC_MEASUREMENT="POSIX" LC_IDENTIFICATION="POSIX" LC_ALL= The output of the same command on the non-working machine: LANG=en_US LANGUAGE=en_NL:en_US:en_GB:en LC_CTYPE="en_US" LC_NUMERIC="en_US" LC_TIME="en_US" LC_COLLATE="en_US" LC_MONETARY="en_US" LC_MESSAGES="en_US" LC_PAPER="en_US" LC_NAME="en_US" LC_ADDRESS="en_US" LC_TELEPHONE="en_US" LC_MEASUREMENT="en_US" LC_IDENTIFICATION="en_US" LC_ALL= If this is not what you mean, could you help me in the right direction? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgress 8.1.4 deadlocking??
On Sep 27, 2006, at 7:28 AM, Rafal Pietrak wrote: Hi, I fell into the following problem (unfortunately, the database contents has sensitive customer information, so can publish very little of that). Currently postgress process takes close to 100% CPU time. I've restarted the process a moment ago, and it was calm for a brief minute. It started to consume the CPU once I've issued: ALTER GROUP majster DROP USER rafal; ... which took close to 5minutes to complete. Then I've issued: Sounds like it was blocked (unsure by what). You can use pg_locks to check that. VACUUM ANALYZE ; ... which I had to cancel when it didnt' completed within another 10minutes. CPU utilisation remains at 100% even after the VACCUM was canceled. VACUUM sometimes hangs around for a while after it is cancelled, which can be annoying. I think this has been improved for 8.2 FWIW What was consuming all the CPU? I assume from what you wrote previously that it is a postgres backend. If so, what was it doing? You can use ps, the pg_stat_activity view and strace to figure that out. pg_dumpall was successfull during the large-CPU-usage time. ...another postgress-process restart, and another VACCUM ANALYSE, this one completes in 3-5 secs. Now "ALTER GROUP ..." goes OK. And everything seams working. Was something else using the database previously? Was something else different this time than the last? the database is currently almost empty: c.a. 100k records within c.a. 20 tables with another 30 or so views. There are c.a. 6k userids (roles) created in the cluster. How big is the data on disk? Is it possible that you have queries scanning tables containing lots of dead tuples? If so a VACUUM FULL would help, but that's totally speculative. Any ideas where should I look (system tables? process stats??) to dieagnose the problem? just to be prepared when it appears again? Postgres itself should not spontaneously start using all of the cpu. Some query operations must be running unless there is an underlying problem (hardware, os, fs). When you saw this happen did you happen to check if it was user or system cpu usage? I had a corrupt filesystem once that cause pg to go off into the weeds consuming all cpu forever, but it was all *system* cpu time. Also I couldn't shut pg down because it was presumably caught in an eternal system call unable to respond to signals. Your description above sounds different than this to me, however. -Casey ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Sockets and posgtres
Jim Nasby wrote: If I'm understanding what you're trying to do (have a function connect to an external process?), I don't think anything exists right now. But it shouldn't be too hard to write something to do that. You might want to create a generic utility and put it on pgFoundry in case others find it useful. Oracle has a package that allows for TCP (as well as other communications) from the database; looking at their interface might be useful. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq You can easily do that with PLperl The funtion then connects to the server running on localhost, and sends some commands terminated with CRLF. CREATE or REPLACE FUNCTION public.psendpopup(text,text) RETURNS pg_catalog.varchar AS $BODY$ use IO::Socket; $sock = new IO::Socket::INET ( PeerAddr => 'localhost', PeerPort => '32000', Proto => 'tcp', ); die "Could not create socket: $!\n" unless $sock; print $sock "null\r\n"; print $sock "send_broadcast\r\n"; print $sock $_[0]."\r\n"; print $sock $_[1]."\r\n"; close($sock); $BODY$ LANGUAGE 'plperlu' VOLATILE; -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Dead Lock problem with 8.1.3
Alban Hertroys wrote:
>>> SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid =
>>> 34 AND s.sid NOT IN ( SELECT sid FROM stud_vera WHERE veraid = 2 );
>
> I'm pretty sure it's not a deadlock. It probably takes very long for
> some reason; maybe an explain of that query will give some insight. You
> probably lack some indices.
No. The system goes into an endless loop. The part ('SELECT sid FROM
stud_vera WHERE veraid = 2') seems to create a temporary table again and
again and again
The same clause needs around 5 seconds under Postgres 8.0.8. On 8.1.3 we
killed the process after 40 hours while using constantly 80% CPU power.
(Btw. Explain leads to the same problem, it just hangs up...)
We're currently trying to separate the problem into a simple setup.
> Alternatively, have you tried an EXCEPT query? I think this is what you
> try to query:
>
> SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid = 34
> EXCEPT
> SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid = 2
Thanks, I will try this out. To be honest, I didn't know the EXCEPT
query (and I think the programmer also doesn't *g*).
*greets*
Kai
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Dead Lock problem with 8.1.3
Joe Conway wrote:
> Martijn van Oosterhout wrote:
>> It probably has something to with the fact that you didn't explain what
>> you meant by "deadlock". Also, you refer to a temp table, yet don't
>> indicate which table it is.
Deadlock means it hangs up and doesn't terminate through timeout.
The system goes into an endless loop. The part ('SELECT sid FROM
stud_vera WHERE veraid = 2') seems to create a temporary table again and
again and again
>> You'll need to be a lot more specific about your problem before we can
>> help. For example, a script to reproduce the problem, or a copy of psql
>> output demonstrating it.
There is no output. It just takes forever. We're currently working to
seperate this problem into a simple setup. If we have one, I will post it.
> Also EXPLAIN output, and information regarding the number of rows in
> each table, and the number of rows matching veraid = 2 and veraid = 34
> might help.
Explain produces the same problem. It just takes forever...
> While the query is running, how much CPU is the process consuming, and
> what does vmstat show for disk and swap I/O?
~80% CPU power. Disk usage is not noticable.
Thanks and
*greets*
Kai
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Dead Lock problem with 8.1.3
Tom Lane wrote: > Kai Hessing <[EMAIL PROTECTED]> writes: >> No one any idea? *sigh* > > What makes you think it's a deadlock and not a very slow query? I'd be > checking if the tables were all ANALYZEd and comparing EXPLAIN output > to the old database ... *hmm* the difference of 5 seconds on 8.0.8 compared to killing the process after 40 hours? All databases are ANALYZEd and EXPLAIN output cannot be compared because it hangs also up on 8.0.8. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Dead Lock problem with 8.1.3
Kai Hessing wrote:
Deadlock means it hangs up and doesn't terminate through timeout.
The system goes into an endless loop. The part ('SELECT sid FROM
stud_vera WHERE veraid = 2') seems to create a temporary table again and
again and again
It is possible for a query to run for many days, and still finish. This
classifies as slow, not hung. The difference is important in
troubleshooting to determine the cause.
Also EXPLAIN output, and information regarding the number of rows in
each table, and the number of rows matching veraid = 2 and veraid = 34
might help.
Explain produces the same problem. It just takes forever...
Did you try EXPLAIN, or EXPLAIN ANALYZE? The former only does the
planning, the latter actually materializes the result in order to get
actual timings, and will therefore take at least as long as the query
itself.
While the query is running, how much CPU is the process consuming, and
what does vmstat show for disk and swap I/O?
~80% CPU power. Disk usage is not noticable.
Can you attach with a debugger and see exactly what's going on? If not
we'd need that self contained test case to reproduce the problem.
Joe
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Dead Lock problem with 8.1.3
On Wed, Sep 27, 2006 at 06:46:42PM +0200, Kai Hessing wrote: > > Deadlock means it hangs up and doesn't terminate through timeout. No, it doesn't. Deadlock means, for the two deadlocked queries, both cannot possibly finish because each waits on a lock that the other one holds. You can cause such deadlocks in your application, too, of course, but they're not database deadlocks. Also. . . > There is no output. It just takes forever. . . .define "forever". Is it doing any work? Do you see i/o? Is it in SELECT WAITING state? A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] How to create nightly backups in Linux
I'm using the the following scheduler script to create nightly backups in Windows: set pgpassword=mypass set FILENAME=%DATE:~8,4%%DATE:~5,2%%DATE:~2,2%mybackup.backup "C:\Program Files\PostgreSQL\8.1\bin\pg_dump.exe" -i -Z9 -b -v -f "%FILENAME%" -F c -h localhost -U postgres mydb I'm bit new to Linux. I'm using white-box linux and Postgres 8.1.4 How to create backups of database with unique name in every night ? Is there some script sample which can be called from /etc/crontab ? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] cannt setup postgresql database for my opennms
I am using linux Red Hat 8 OS, it may be 2 old now, ^_^ I have just installed my opennms by rpm package, and now i am running the installer to setup the PostgreSQL database: # $OPENNMS_HOME/bin/install -disU but some exception happens: == OpenNMS Installer Version $Id: Installer.java 3056 2006-03-26 19:39:16Z djgregor $ == Configures PostgreSQL tables, users, and other miscellaneous settings. Exception in thread "main" org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJdbc1Connection.java:204) at org.postgresql.Driver.connect(Driver.java:139) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at org.opennms.install.Installer.databaseConnect(Installer.java:613) at org.opennms.install.Installer.install(Installer.java:195) at org.opennms.install.Installer.main(Installer.java:2450) i have just now checked my postgre with command "netstat -anp | grep "post"" and they are right: udp0 0 127.0.0.1:32968 127.0.0.1:32968 ESTABLISHED 5850/postmaster unix 2 [ ACC ] STREAM LISTENING 123166 5850/postmaster /tmp/.s.PGSQL.5432 why dont my opennms setup datebase successfully on my postgresql? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] column names in select don't exists in insert to
Michael Fuhr schreef: > On Thu, Sep 21, 2006 at 01:32:47AM -0700, Johan wrote: > > Someone executed the query in psql for me and the problem seems to be > > in a stored procedure triggered after update. This procedure complains > > about the field. > > Were you able to fix the problem or are you still uncertain what > the problem is? If the problem still exists then please post a > simple but complete test case, including the exact error message > you're getting. > > -- > Michael Fuhr > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match I was able to fix te problem. Now the statement is working fine Thanks ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] change the order of FROM selection to make query work
Am 26.9.2006 schrieb "Tom Lane" <[EMAIL PROTECTED]>:
>Define "stopped working" ... what was wrong exactly?
oh, sorry. i forgot the error message:
Error: org.postgresql.util.PSQLException: ERROR: invalid reference to
FROM-clause entry for table "t", SQL State: 42P01, Error Code: 0
(i use squirrel-sql)
the trac (through python&pgsql) error was:
"Report execution failed: ERROR: invalid reference to FROM-clause entry
for table "t" HINT: There is an entry for table "t", but it cannot
be referenced from this part of the query."
but the code in the OP doesn't produce the error (i simplified the sql,
but propably striped the error as well)
the full code that does produce the error (and this error can be resolved
as in OP described) is:
SELECT
(CASE WHEN d.value = Null THEN '9' ELSE d.value END)as
Wiedervorlage,
p.value AS __color__,
id AS ticket, summary, status, priority ,component, t.type AS type,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS
owner,
time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter,
(CASE WHEN c.value = '0' THEN 'None' ELSE c.value END) AS
Fachabteilung,
(CASE WHEN e.value = '0' THEN 'None' ELSE e.value END) AS Kategorie
FROM ticket as t, permission as perm, enum as p
LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name =
'fachabteilung')
LEFT Outer join ticket_custom d ON (t.id = d.ticket AND d.name =
'wiedervorlage')
LEFT Outer join ticket_custom e ON (t.id = e.ticket AND e.name =
'kategorie')
WHERE status IN ('new', 'assigned', 'reopened') AND perm.action =
'mf' and perm.username='$USER'
and p.name = t.priority AND p.type='priority'
ORDER BY wiedervorlage, priority, p.value, t.type, time
cheers,
thomas
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Postgresql reindex hangs when table is being updated
Hi all, I'm running a web application supported by postgresql 7.4 on an unix system. The management part of application requires reindexing of tables. I see that whenever reindexing runs with the while the table is getting updated/inserted into, reindexing hangs. I understand this is because reindexing requires an exclusive lock on the table. Is there a possible workaround? What else can I do other than stopping the insertion/update while reindex runs? Thanks, Walter ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] in failed sql transaction
Sorry, I was a bit impatient and posted the same question in a newsgroup a few days before. There is an answer now: http://groups.google.de/group/comp.databases.postgresql/browse_thread/thread/36e5c65dd15b0388/1e5ff9b7e2c6863e?hl=de#1e5ff9b7e2c6863e Of course, if anyone has an additional idea, i'd appreciate it. Best regards, Ralf. -- Ralf Wiebicke Software Engineer exedio GmbH Am Fiebig 14 01561 Thiendorf Deutschland Telefon +49 (35248) 80-118 Fax +49 (35248) 80-199 [EMAIL PROTECTED] www.exedio.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] What is the Best Postgresql Load Balancing Solution available ?
You can try using pg_pconnect instead of pg_connect. It has some downsides so see the docs. Also - check your memory usage, it may be you could fix this by reducing work_mem or similar. Regards, Ben "Najib Abi Fadel" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]...Hi again,How can i use connection pooling ? Should i use a software like PGPool ? Will the connection pooling boost considerably the performance ?Leonel adviced me to use persistent connections ? hos do i use that ?PS: I am using PHP for my applications.ThanksNajibTalha Khan <[EMAIL PROTECTED]> wrote: You should also consider using connection pooling inorder to attain better performance. Regards Talha Khan On 9/20/06, Najib Abi Fadel <[EMAIL PROTECTED]> wrote: I have a web application that is accessed by a large number of users. My application is written in PHP and uses postgres. Apache is our web server.The performance of my application drops down when a large numbers of users connects at the same time. I need to have a better response time ! That's why i need to load balance the web requests and the database. Regards,Najib.Ben Trewern <[EMAIL PROTECTED]> wrote: The solution you need all depends on the problem you are having. If you explain how your application is written PHP, Java, etc and where your performance problems are coming from, then someone could give you a better answer!Regards,Ben "Najib Abi Fadel" wrote in message news:[EMAIL PROTECTED] Robin Ericsson wrote:On 9/18/06, Najib Abi Fadel wrote:> Hi,>> i was searching for a load balancing solution for> postgres, I found some ready to use software like > PGCluster, Slony, pgpool and others.>> It would really be nice if someone knows which one is> the best taking in consideration that i have an> already running application that i need to load > balance.There isn't one tool that is the best, all three work very good basedon where they are used and what they are used for.-- regards,Robin---(end of broadcast)--- TIP 5: don't forget to increase your free space map settingsDid you try them or have any experience with them. I need them for load balancing my database and thus making the queries faster. I have a web application heavely using a postgres database. Hundreds of users can connect at the same time to my web application.Thanks in advance for any help.Najib.How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. ---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. Do you Yahoo!?Next-gen email? Have it all with the all-new Yahoo! Mail.
[GENERAL] hi, what is wrong with my newbie sql?
Hi, the following sql returns "10" and not "20" as would be expected. Where is the error? This would for sure work in any programming language, why is this different? CREATE FUNCTION test () RETURNS INTEGER AS ' DECLARE k integer; BEGIN k = 10; FOR k IN 1..10 LOOP k = k +1; END LOOP; return k; END; ' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Solution for rolling back many transactions?
The most cumbersome part of our many (hundreds) of unit tests that verify our data access is working, is creating and then deleting all of the test data. Currently, we're doing it at a pretty high level using java and our ORM (hibernate). It occurred to me that I can probably speed the deletion up by implementing a stored procedure to help do the cleanup. This way we don't pay the penalty for a lot of object construction and individually-issued delete statements.. This might help, but half way through writing it I wondered if there's an even easier solution... What I want is to be able to roll the state of the database back to the way it was when the test began. Essentially, I want the same behavior I'd get if I backed the database up and then re-imported it afterward.. Only I need the process to add very little overhead (less than a second). Are there any creative ways to accomplish this? I figured I'd ask the list before trudging forward with my still-not-optimal stored procedure. In case it's useful information, the test database is pretty small... Maybe 15 or 20 megs. The unit tests are individually pretty small.. Less than 100 inserts each, for sure... So there's not that much to 'roll back'.. As far as using transactions to undo the state of the database, let me know if that's possible.. All of our tests have their own transaction boundaries that they're applying. I don't want to undo that, but certainly if I could nest each test in a longer- running tx and then roll it back.. that could work. Thanks in advance. Phill ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] What is the Best Postgresql Load Balancing Solution available ?
What is the problem with pg_pconnect and is pgpool easy to use with an already running application ?Should i expect a major performance boost ? Najib."Joshua D. Drake" <[EMAIL PROTECTED]> wrote: Nikolay Samokhvalov wrote:> As for persistent connection with PHP, start from here:> http://php.net/pg_pconnect.Uhmmm no.Start here: http://pgpool.projects.postgresql.org/pg_pconnect tends to have a host of issues.Joshua D. Drake> > Also, I recommend to ensure that you have proper set of indexes on> your tables and your system doesn't use badly written queries (such as> join of several dozens of tables :-) ). There is an excellent tool> that can help you monitoring your database, pgFouine:> http://pgfouine.projects.postgresql.org/. I recommend it highly, it> really helps to understand what queries are "bottle neck" of your> system and when (the time of the day) the load reaches maximum.> > Good luck.> > On 9/21/06, Najib Abi Fadel <[EMAIL PROTECTED]> wrote:>> Hi again, How can i use connection pooling ? Should i use a software like PGPool ?>> Will the connection pooling boost considerably the performance ? Leonel adviced me to use persistent connections ? hos do i use that ? PS: I am using PHP for my applications. Thanks>> Najib Talha Khan <[EMAIL PROTECTED]> wrote: You should also consider using connection pooling inorder to attain >> better>> performance. Regards>> Talha Khan>> On 9/20/06, Najib Abi Fadel <[EMAIL PROTECTED]> wrote:>> >>> > I have a web application that is accessed by a large number of users.>> > My application is written in PHP and uses postgres. Apache is our web>> server.>> > The performance of my application drops down when a large numbers of >> users>> connects at the same time. I need to have a better response time ! >> That's>> why i need to load balance the web requests and the database.>> >>> > Regards,>> > Najib.>> >>> > Ben Trewern <[EMAIL PROTECTED]> wrote:>> >>> >>> > The solution you need all depends on the problem you are having. If you>> > explain how your application is written PHP, Java, etc and where your>> > performance problems are coming from, then someone could give you a >> better>> > answer!>> >>> > Regards,>> >>> > Ben>> >>> >>> > "Najib Abi Fadel" wrote in message>> >>> >>> news:[EMAIL PROTECTED]>> >>> >>> >>> > Robin Ericsson wrote:>> > On 9/18/06, Najib Abi Fadel wrote:>> > > Hi,>> > >>> > > i was searching for a load balancing solution for>> > > postgres, I found some ready to use software like>> > > PGCluster, Slony, pgpool and others.>> > >>> > > It would really be nice if someone knows which one is>> > > the best taking in consideration that i have an>> > > already running application that i need to load>> > > balance.>> >>> > There isn't one tool that is the best, all three work very good based>> > on where they are used and what they are used for.>> >>> > -->> > regards,>> > Robin>> >>> > ---(end of>> broadcast)--->> > TIP 5: don't forget to increase your free space map settings>> >>> > Did you try them or have any experience with them. I need them for load>> > balancing my database and thus making the queries faster. I have a web>> > application heavely using a postgres database. Hundreds of users can>> connect>> > at the same time to my web application.>> >>> > Thanks in advance for any help.>> >>> > Najib.>> >>> >>> >>> >>> >>> >>> > How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call>> rates.>> >>> >>> >>> > ---(end of>> broadcast)--->> > TIP 1: if posting/reading through Usenet, please send an appropriate>> > subscribe-nomail command to [EMAIL PROTECTED] so that your>> > message can get through to the mailing list cleanly>> >>> >>> >>> >>> > >>> > How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call>> rates.>> >>> >>> >> Do you Yahoo!?>> Next-gen email? Have it all with the all-new Yahoo! Mail.> > -- === The PostgreSQL Company: Command Prompt, Inc. ===Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail.
[GENERAL] How to Examine a view
I have setup a view consisting of two tables and some compound fields. Now I would like from the client perspective see what those compound fields actually are originally. Let's (for simplicity I have inlcuded only one table) say we have: CREATE OR REPLACE VIEW clientview AS SELECT c.clientid, (c.orderer_name::text || ' :'::text) || substr(c.occasion::text, 1, 10) AS "Order occasion", c.customerid FROM client c Is it possible to query postgres to tell me what field names and field types "Order occasion" really consists of? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] serial column
Title: RE: [GENERAL] serial column
Yes, but if I tear down the house at 245 Main St, we don't renumber 247 Main St and on down the line, do we?
The problem here is that even if you get the sequencing to work, your table is dangerously unstable. If you have 700 rows and you delete row #200, suddenly rows 201-700 are wrong. That means you can't just lock the row you're working on. You'll need to lock the whole table from INSERT/UPDATE/SELECT/DELETE until you've rebuilt the table because the whole thing is suddenly false.
I still believe the best method is going to be the linked list method I suggested, and it's the only one I can think of that meets relational model requrements. The problem with it is that while finding the first item (the one with NULL parent) and last item (the one with NULL child) are easy, and deleting any item is easy (parent becomes parent of child, delete record), and even inserting an item anywhere is easy (insert new record, new record becomes child of parent and parent of parent's child), it's more difficult to ask for item #4 in the order or item #261 in the order. You need an index for your linked list, which I'm guessing is precisely the problem. :) External indices to linked lists is another thing an SQL database doesn't precisely handle very well, since it's all metadata and that adds to physical overhead.
So we return to the question: what purpose does this sequential order serve? Why are gaps bad? What problems are gaps causing? Why does the database need to know the exact order? Why can't your control code be aware of it instead?
You're asking the RDBMS to do something it was exactly designed *not* to do. Rows are supposed to be unrelated objects or entries. A table is *not* a tuple or an array. Ordering them relates them, and makes your data less independent and your database less normalized.
In any case, I strongly recommend against using the ordering field as the primary key simply because you're planning to change them so much. Make it a unique key to enforce the constraint, but primary keys should generally be very stable fields.
Brandon Aiken
-Original Message-
From: Bob Pawley [mailto:[EMAIL PROTECTED]]
Sent: Mon 9/25/2006 11:59 AM
To: Brandon Aiken; pgsql-general@postgresql.org
Subject: Re: [GENERAL] serial column
Actually, I am not trying to "force keys" nor, I don't beleive, am I trying
to force an hierarchal structure within the database.
The numbers I want to assign to devices are nothing more than merely another
attribute of the device - perhaps akin to a number in a street address. The
problem, from my viewpoint, is that this attribute needs to always start at
1 and be sequential without gaps.
(I am however, partly relying on an hierarchal order within the database.
When I assign numbers to devices, the lowest number is assigned,
sequentially, to the device that has the lowest serial ID number. )
Thanks for your comments - everything helps at my stage.
Bob Pawley
- Original Message -
From: "Brandon Aiken" <[EMAIL PROTECTED]>
To:
Sent: Monday, September 25, 2006 7:47 AM
Subject: Re: [GENERAL] serial column
I would tend to agree with Tom.
A table is by definition an unordered set of records. Forcing keys to
have meaning of this type implies that there is a relationship between
each record in the set. That's information you should be storing as
part of the record. If order is important, design the database so that
it knows that order relationship exists.
An ordered list is just a hierarchal database wherein every record has
exactly one parent (or none if it's root) and exactly one child (or none
if it's end leaf), but the relational model does a rather poor job of
handling hierarchal relationships. You might consider the two-way
linked list approach. That is, each record knows the item before it and
the item after it, like so:
TABLE mainTable
{
id serial PRIMARY KEY,
foo text,
bar integer,
zen numeric
}
TABLE mainTableRelationships
{
parentID integer,
childID integer,
CONSTRAINT "mainTableRelationships_pkey" PRIMARY KEY
("parentID", "childID"),
CONSTRAINT "parentID_key" UNIQUE ("parentID"),
CONSTRAINT "childID_key" UNIQUE ("childID"),
CONSTRAINT "mainTable_parentID_fkey" FOREIGN KEY ("parentID")
REFERENCES "mainTable" ("id"),
CONSTRAINT "mainTable_childID_fkey" FOREIGN KEY ("childID")
REFERENCES "mainTable" ("id")
}
Of course, there's really little difference between doing things this
way and ordering by the SERIAL field and numbering them appropriately on
output, except that this above way is hideously more complex.
Another option would be to create a temporary table ordered correctly,
truncate the existing table, delete the sequence (or change the default
on the primary key), copy the data back, and then re-create the sequence
(or change default back to nextval) and then set nextval to MAX()+1.
This is rather ugly, however, since you're still forcing the database to
do relationship
[GENERAL] dbi-link questions + patch
Hi all :)
first, sorry for crossposting but dbilink mailinglist is extremely low traffic
so I decided to mail this also to pgsql-general
I'm looking for a solution that gives PostgreSQL "remote server" aka "proxy
tables" functionality.
We're trying here to evaluate dbi-link, and have some problems.
I got version 1.0.0 from PgFoundry.
First I had to do some fixes to make it install on PostgreSQL 8.1 with Perl
5.8.8 (the patch is below).
but then I also had some problems.
question 1)
-- PREPARATION (stripped output/diagnostic messages):
pgdba=# CREATE DATABASE local;
pgdba=# CREATE DATABASE remote;
pgdba=# \c remote
remote=# CREATE TABLE tab1(id bigserial, data text);
remote=# insert into tab1(data) values('AAA');
remote=# \c local
local=# CREATE LANGUAGE 'plperlu';
local=# \i dbi_link.sql
local=# SELECT dbi_link.make_accessor_functions(
local-# 'dbi:Pg:dbname=remote;host=localhost;port=5810', 'pgdba',
local-# NULL, '{AutoCommit => 1, RaiseError => 1}', 'public', NULL, remote');
-- TEST 1
local=# select * from remote.tab1;
NOTICE: Connected to database
NOTICE: sql is
COMMENT ON COLUMN tab1_rowtype.id IS 23361
bigint
23361
id | data
+--
1 | AAA
(1 row)
- it is OK but... why these NOTICEs? is it normal behaviour?
-- TEST 2
local=# insert into remote.tab1(data) values('BBB');
ERROR: error from Perl trigger function: column "ad" does not exist at line 28.
- it is definitely not OK. what could be the problem here?
question 2)
why is DBI-Link marked as "stable" on PgFoundry?
if it does not even work out-of-the-box, it is not mature yet.
maybe it should be "beta"?
question 3)
did anyone try to establish DBI-Link between UTF8 encoded database and non-UTF8
encoded database? i'm asking because i got:
utf8db=# select count(*) from remotelatin2db.dict01;
(...)
ERROR: invalid UTF-8 byte sequence detected near byte 0xf3
question 4) could anyone please suggest some software that gives similar
functionality (and works)?
regards && thanks for your time
Filip
dbi-link-1.0.0 patch begin.
diff -Naur dbi-link-1.0.0/make_connection.sql
dbi-link-1.0.0.1/make_connection.sql
--- dbi-link-1.0.0/make_connection.sql 2005-01-26 09:47:11.0 +0100
+++ dbi-link-1.0.0.1/make_connection.sql2006-09-27 13:12:14.0
+0200
@@ -36,7 +36,7 @@
, db_password => $db_password
);
-return TRUE;
+return 'TRUE';
sub check_connection {
my %parms = (
diff -Naur dbi-link-1.0.0/remote_query.sql dbi-link-1.0.0.1/remote_query.sql
--- dbi-link-1.0.0/remote_query.sql 2005-01-26 09:47:11.0 +0100
+++ dbi-link-1.0.0.1/remote_query.sql 2006-09-27 13:15:12.0 +0200
@@ -164,7 +164,7 @@
FROM dbi_link.dbi_connection
WHERE ad = $data_source_id
SQL
-my ($data_source, $user, $auth, $dbh_attr);
+my ($data_source, $user_name, $auth, $dbh_attr);
my $driver_there = spi_exec_query($dtsql);
my $nrows = $driver_there->{processed};
if ($nrows == 0) {
@@ -260,6 +260,8 @@
RETURNS TRIGGER
LANGUAGE plperlu
AS $$
+our %_TD;
+my $user_name;
#
# #
# Immediately reject anything that's not an INSERT. #
@@ -371,7 +373,7 @@
INSERT INTO $table (
@{[join("\n, ", sort keys %$new) ]}
) VALUES (
- @{[join("\n, ", { $new->{$_} } sort keys %$new) ]}
+ @{[join("\n, ", map { $new->{$_} } sort keys %$new) ]}
)
SQL
my $sth = $dbh->prepare($sql);
@@ -379,7 +381,7 @@
}
sub update {
-my $table = $_TD{relname}
+my $table = $_TD{relname};
my $sql = <
Re: [GENERAL] Postgresql reindex hangs when table is being updated
On Tue, 2006-09-26 at 23:31, deep ... wrote: > Hi all, > > I'm running a web application supported by postgresql 7.4 on an unix > system. The management part of application requires reindexing of > tables. I see that whenever reindexing runs with the while the table > is getting updated/inserted into, reindexing hangs. I understand this > is because reindexing requires an exclusive lock on the table. > > > Is there a possible workaround? What else can I do other than stopping > the insertion/update while reindex runs? What problem are you trying to solve? Perhaps something other than reindex might work. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] continued segmentation fault
We continue to have segmentation faults of the /usr/bin/postgres process as I mentioned in an earlier thread. In all cases, the core file always indicates a segmentation fault, but the backtraces don't seem to consistently point to any particular problem. Then again, when you go stomping around in memory where you don't belong, all bets are probably off. I'm wondering out loud (here) if anyone thinks it might have something to do with the version we're on? 7.4.7. We are planning to upgrade to 7.4.13 soon, but were hoping to address this existing issue first. It's a bit difficult debugging this issue as I must initiate gdb via the client. I have yet to reproduce this problem in my test environment, but then again, I'm running my single debugging client, whereas in the production system, there could well be 150-200 clients running. I've attached the latest backtrace, in the event anyone sees anything glaringly obvious, please slap me in the head... Thanks in advance. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin Using host libthread_db library "/lib/tls/libthread_db.so.1". Core was generated by `postgres: chuck sev [local] SELECT'. Program terminated with signal 11, Segmentation fault. #0 0x0815c973 in cmtreefree (cm=0x9925064, tree=0x0, level=2) at regc_color.c:125 in regc_color.c #0 0x0815c973 in cmtreefree (cm=0x9925064, tree=0x0, level=2) at regc_color.c:125 #1 0x0815c9bc in cmtreefree (cm=0x9925064, tree=0x98a1528, level=1) at regc_color.c:131 #2 0x0815c9bc in cmtreefree (cm=0x9925064, tree=0x9925144, level=0) at regc_color.c:131 #3 0x0815c8f1 in freecm (cm=0x9925064) at regc_color.c:97 #4 0x0815b458 in rfree (re=0x800) at regcomp.c:2105 #5 0x08162825 in pg_regfree (re=0x9925064) at regfree.c:53 #6 0x081aa8ad in RE_compile_and_execute (text_re=0x9f171a0, dat=0x800 , dat_len=170796744, cflags=11, nmatch=0, pmatch=0x0) at regexp.c:212 #7 0x081aafd2 in texticregexne (fcinfo=0xfeffac80) at regexp.c:387 #8 0x08107634 in ExecMakeFunctionResult (fcache=0x9f54ff0, econtext=0x9f548e8, isNull=0xfeffad9b "", isDone=0x0) at execQual.c:907 #9 0x081091a0 in ExecEvalExpr (expression=0x9f54ff0, econtext=0x9f548e8, isNull=0x0, isDone=0x9925064) at execQual.c:2257 #10 0x08109dbb in ExecQual (qual=0x9f54b10, econtext=0x9f548e8, resultForNull=0 '\0') at execQual.c:2859 #11 0x0810a1e1 in ExecScan (node=0x9f54860, accessMtd=0x8112150 ) at execScan.c:129 #12 0x08112239 in ExecSeqScan (node=0x800) at nodeSeqscan.c:131 #13 0x081060d6 in ExecProcNode (node=0x9f54860) at execProcnode.c:306 #14 0x0810e13b in ExecHash (node=0x9f54730) at nodeHash.c:81 #15 0x081061b5 in ExecProcNode (node=0x9f54730) at execProcnode.c:364 #16 0x0810ec6c in ExecHashJoin (node=0x942bbc8) at nodeHashjoin.c:128 #17 0x08106167 in ExecProcNode (node=0x942bbc8) at execProcnode.c:337 #18 0x0810f03b in ExecHashJoinOuterGetTuple (node=0x800, hjstate=0x942b460) at nodeHashjoin.c:510 #19 0x0810ea94 in ExecHashJoin (node=0x942b460) at nodeHashjoin.c:152 #20 0x08106167 in ExecProcNode (node=0x942b460) at execProcnode.c:337 #21 0x0810d288 in agg_fill_hash_table (aggstate=0x942b850) at nodeAgg.c:905 #22 0x0810cec7 in ExecAgg (node=0x942b850) at nodeAgg.c:654 #23 0x0810619b in ExecProcNode (node=0x942b850) at execProcnode.c:356 #24 0x08104a1d in ExecutePlan (estate=0x942b398, planstate=0x942b850, operation=CMD_SELECT, numberTuples=10, direction=2048, dest=0x826d134) at execMain.c:1100 #25 0x08103df8 in ExecutorRun (queryDesc=0x9f17848, direction=ForwardScanDirection, count=2048) at execMain.c:249 #26 0x0817b07b in PortalRunSelect (portal=0x9439c98, forward=0 '\0', count=10, dest=0x826d134) at pquery.c:590 #27 0x0817b6d3 in PortalRunFetch (portal=0x9439c98, fdirection=2048, count=2048, dest=0x800) at pquery.c:961 #28 0x08117c67 in _SPI_cursor_operation (portal=0x9439c98, forward=1 '\001', count=10, dest=0x826d134) at spi.c:1315 #29 0x081171eb in SPI_cursor_fetch (portal=0x800, forward=1 '\001', count=2048) at spi.c:881 #30 0x00a0ca39 in exec_stmt_fors (estate=0xfeffb210, stmt=0x950b188) at pl_exec.c:1391 #31 0x00a0c10e in exec_stmt (estate=0xfeffb210, stmt=0x950b188) at pl_exec.c:963 #32 0x00a0c005 in exec_stmts (estate=0xfeffb210, stmts=0x96fc0d8) at pl_exec.c:903 #33 0x00a0be15 in exec_stmt_block (estate=0xfeffb210, block=0x944ba68) at pl_exec.c:859 #34 0x00a0b061 in plpgsql_exec_function (func=0x96069c8, fcinfo=0xfeffb2f0) at pl_exec.c:325 #35 0x00a07ff4 in plpgsql_call_handler (fcinfo=0xfeffb2f0) at pl_handler.c:124 #36 0x08107634 in ExecMakeFunctionResult (fcache=0x9f689a0, econtext=0x9f68928, isNull=0xfeffb41b "", isDone=0x9f68c30) at execQual.c:907 #37 0x0810918a in ExecEvalExpr (expression=0x9f689a0, econtext=0x9f68928, isNull=0x0, isDone=0x9925064) at execQual.c:2253 #38 0x
Re: [GENERAL] Strange query results with invalid multibyte
Joost Kraaijeveld <[EMAIL PROTECTED]> writes: > On Wed, 2006-09-27 at 12:10 -0400, Tom Lane wrote: >> What you need to find out next is >> what character set encoding that locale implies on your machine. I'm >> betting it's not utf8 though :-( > If this is not what you mean, could you help me in the right direction? Try this command: LANG=en_US locale charmap regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] strange sql issue
hi,
i support a trac [1] installation and changed the backend from sqlite to
postgres 8.1.4
the following sql stopped working with postgres, and the fix of this
problem seems strange to me.
first the old sql, that worked with sqlite:
SELECT
p.value AS __color__,
id AS ticket, summary, status, priority ,component, t.type AS type,
time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter,
FROM ticket as t, permission as perm, enum as p
WHERE status IN ('new', 'assigned', 'reopened') AND perm.action =
'mf'
and p.name = t.priority AND p.type='priority'
ORDER BY priority, type, time
and the fix was, to twist the order in the FROM statement.
changing
FROM ticket as t, permission as perm, enum as p
to
FROM permission as perm, enum as p, ticket as t
works like expected!
so is this a bug, or do i get soemthing wrong (a hint where to rtfm would
be very welcome in this case)
thanx,
thomas
[1] trac.edgewall.org
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] change the order of FROM selection to make query work
On Tue, 2006-09-26 at 09:19, Tom Lane wrote: > "Thomas Peter" <[EMAIL PROTECTED]> writes: > > the full code that does produce the error (and this error can be resolved > > as in OP described) is: > > Never oversimplify a bug report. > > > FROM ticket as t, permission as perm, enum as p > > LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name = > > 'fachabteilung') > > The above is, plain and simple, wrong. According to the SQL spec, > JOIN binds more tightly than comma in a FROM-list, so what you had was > > FROM ..., (enum as p > LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name = > 'fachabteilung')) > > which of course fails because only p and c are visible in the JOIN's > ON condition. You fixed it by moving "t" to become part of the JOIN > structure. > > I was aware that MySQL parses this sort of structure wrongly, but it's > disappointing to hear that sqlite does too :-( And I think MySQL fixed this abberant behaviour in the newest beta. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] What is the Best Postgresql Load Balancing Solution
On Wed, 2006-09-27 at 00:22, Najib Abi Fadel wrote: > What is the problem with pg_pconnect and is pgpool easy to use with an > already running application ? > Should i expect a major performance boost ? pg_pconnect isn't "pooling" per se. In pooling, a large number of processes share a small number of connections. I.e. 200 php processes would access the database by sharing 20 or 30 connections per database accessed. So, if you had 3 databases, each would have 20 or so connections to it, and the 200 odd php processes would have to wait for a free connection or would error out depending on how pooling was set up. Note that php doesn't directly support this type of pooling, like jdbc does. pg_pconnect creates a persistent connection for each database accessed by each process. So, if you had the same three databases and 200 php processes that all, at one time or another, hit each of those databases, your database would need to manage 600 connections. This makes it very easy to DOS your database server with too many connections. Pgpool is VERY easy to use, and gets rid of most of the overhead of starting a connection (backend process creation) by holding open x number of connections for you all the time. Therefore, the real cost of pg_connect over pg_pconnect goes away, and you can simply use pg_connect to the pgpool process. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] cannt setup postgresql database for my opennms
"=?utf-8?B?5p6X6bub546J?=" <[EMAIL PROTECTED]> writes: > Exception in thread "main" org.postgresql.util.PSQLException: > Connection refused. Check that the hostname and port are correct and > that the postmaster is accepting TCP/IP connections. > i have just now checked my postgre with command "netstat -anp | grep > "post"" and they are right: > udp0 0 127.0.0.1:32968 127.0.0.1:32968 > ESTABLISHED 5850/postmaster > unix 2 [ ACC ] STREAM LISTENING 123166 > 5850/postmaster /tmp/.s.PGSQL.5432 > why dont my opennms setup datebase successfully on my postgresql? I believe Java requires a TCP connection to postgres; you appear to not have enabled TCP connections. You'll need to adjust the postmaster's listen_addresses or tcpip_socket setting (depending on what PG version this is) and restart it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] hi, what is wrong with my newbie sql?
Ronin <[EMAIL PROTECTED]> schrieb: > Hi, the following sql returns "10" and not "20" as would be expected. > > Where is the error? This would for sure work in any programming > language, why is this different? > > CREATE FUNCTION test () RETURNS INTEGER AS ' > > DECLARE > k integer; > > BEGIN > k = 10; > FOR k IN 1..10 LOOP > k = k +1; > END LOOP; > > return k; > END; I think, the inner k = k + 1; is not visible outer. The loop runs from k=1 to k=10, and the returning k is the last loop-value k, 10. You need a extra variable for the loop-counter. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] hi, what is wrong with my newbie sql?
"Ronin" <[EMAIL PROTECTED]> writes: > k = 10; > FOR k IN 1..10 LOOP > k = k +1; > END LOOP; > return k; An integer for-loop implicitly declares its control variable, so the "k" inside the loop is unrelated to the "k" outside. Hence you get 10. As for what you'd get if they were the same variable, I dunno why you're so certain it would be 20. Munging a loop's control variable by hand is a good way to trip over undocumented details of the loop implementation. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to create nightly backups in Linux
Andrus wrote:
> I'm bit new to Linux. I'm using white-box linux and Postgres 8.1.4
> How to create backups of database with unique name in every night ?
> Is there some script sample which can be called from /etc/crontab ?
I use the following Z-shell script. Rewriting to bash should be
trivial (probably no changes after the first line).
Note that this differs from yours in that it uses pg_dumpall
and dumps everything. You would want to change that...
---
#!/bin/zsh
# We prefer backing up to the SAN, but use the local
# disk as a fallback if the SAN isn't available.
backupdir=/u3/SolisDBbackups
if [ -d /mnt/san/SOLIS-files/db_backups ]; then
backupdir=/mnt/san/SOLIS-files/db_backups
fi
backupdate=$(date --iso-8601=minutes)
backupfile=${backupdir}/${backupdate}.dbtxt
echo "Backing up SOLIS databases to ${backupfile}.gz"
date
pg_dumpall -c | gzip >${backupfile}.gz
echo "Backup of SOLIS databases to ${backupfile}.gz done."
date
exit 0
The crontab entry is:
---
45 21 * * * /u3/SolisDBbackups/fullDump.zsh
---
Hope this helps.
--
Steve Wampler -- [EMAIL PROTECTED]
The gods that smiled on your birth are now laughing out loud.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Cumulative aggregate
Hi all, I want to write an aggregate to sum the values for rain precipitations. I found a working example with integer values, but I cannot find a way to to the same with float ones. Here is what I did : -- table testcreate table mytest (fld1 int4, fld2 float4);insert into mytest values (1, 1.23);insert into mytest values (6, 5.38);insert into mytest values (2, 0.12); -- function for integer (OK)create function myint4_sum(int4,int4) returns int4as 'select int4_sum($1,$2)::int4;'language SQL;-- aggregatecreate aggregate myagg1_sum (basetype = int4, sfunc = myint4_sum, stype = int4);-- resultselect myagg1_sum(fld1) from mytest; --> give 9 -- function for float (NOT WORKING)create function myfloat4_sum(float4,float4) returns float4as 'select float4pl($1,$2)::float4;'language SQL;-- aggregatecreate aggregate myagg2_sum (basetype = float4, sfunc = myfloat4_sum, stype = float4);-- resultselect myagg2_sum(fld2) from mytest; --> NOTHING ??? INSTEAD OF 6.73 What am I doing wrong ?? Thank you very much, Paolo Saudin Italy
Re: [GENERAL] postgress 8.1.4 deadlocking??
Ups, missed the list recepient itself. Thenx Duncan for the analysis. This happend again, so I'm able to peek at the details you've pointed out. On Wed, 2006-09-27 at 09:33 -0700, Casey Duncan wrote: > Sounds like it was blocked (unsure by what). You can use pg_locks to > check that. That view currently contais 240 entries source of blocking. Now I have to dig the ultimate reason why those locks end up there (and are not being cleand). Any ideas? Whould those locks go away by themselves in time? > What was consuming all the CPU? I assume from what you wrote > previously that it is a postgres backend. If so, what was it doing? ps output (just one backend) postgres 4759 0.0 2.0 19368 5292 ?S21:02 0:00 postgres: czyn_738773 czyn 127.0.0.1(55431) UPDATE waiting --- "czyn" is the name of the database, "czyn_738773" is database username. IP address is localhost, since the database purpose is web serbvice back-end. Still, I'm not sure what that UPDATE counld that be. > You can use ps, the pg_stat_activity view and strace to figure that out. - exerpt from pg_stat_activity- 17660 | czyn |4759 |19541 | czyn_738773 | | | 2006-09-27 21:02:05.914728+02 | 127.0.0.1 | 55431 (18 rows) -- But this is not very meaningfull to me. > > ...another postgress-process restart, and another VACCUM ANALYSE, this > > one completes in 3-5 secs. Now "ALTER GROUP ..." goes OK. And > > everything > > seams working. > > Was something else using the database previously? Was something else > different this time than the last? No. My impression was (naive, I know), that not all things were correctly cleand-up on the first restart. Now I think, that on the second go I was just a little bit more lucky - www service was calm for a little longer. That was anough for VACUUM to complete. Still, throuble started later. > How big is the data on disk? Is it possible that you have queries close to nothing: c.a. 200MiB > problem (hardware, os, fs). When you saw this happen did you happen > to check if it was user or system cpu usage? I had a corrupt It's the user CPU time. As it currently looks like some unknown UPDATE causes the trouble, how can I identify what it is? 1. the database is not very sophisticated, apart from what I've mentioned earlier, it does have just a few stored procedures in pgsql - nothing fancy. And there is no triggers just specialized views (although quite a lot of referencial integrity constrains). 2. machine is 'from the old days': 450MHz CPU 3. the system is currently under 'available-for-public' condition, so there may be 'waves' (peeks) of activity. 4. when the system was tested in-lab (small number of users - no risk / not-tested for havier load behavior), I haven't noticed this 'blocking'. Any ideas where should I look now, to pinpoint the problem? -- -R ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] cyclical redundancy checksum algorithm(s)?
I just finished reading one of Ralph Kimball's books. In it he mentions something called a cyclical redundancy checksum (crc) function. A crc function is a hash function that generates a checksum. I am wondering a few things. A crc function would be extremely useful and time saving in determining if a row needs to be updated or not (are the values the same, if yes don't update, if not update). In fact Ralph Kimball states that this is a way to check for changes. You just have an extra column for the crc checksum. When you go to update data, generate a crc checksum and compare it to the one in the crc column. If they are same, your data has not changed. Yet what happens if there is a collision of the checksum for a row? Ralph Kimball did not mention which algorithm to use, nor how to create a crc function that would not have collisions. He does have a PhD, and a leader in the OLAP datawarehouse world, so I assume there is a good solution. Is there a crc function in postgresql? If not what algorithm would I need to use to create one in pl/pgsql? regards, karen ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgress 8.1.4 deadlocking??
On Sep 27, 2006, at 12:35 PM, Rafal Pietrak wrote: Thenx Duncan for the analysis. This happend again, so I'm able to peek at the details you've pointed out. On Wed, 2006-09-27 at 09:33 -0700, Casey Duncan wrote: Sounds like it was blocked (unsure by what). You can use pg_locks to check that. That view currently contais 240 entries source of blocking. Now I have to dig the ultimate reason why those locks end up there (and are not being cleand). Any ideas? You need to look at the procpid that's holding the lock and then use pg_stat_activity to see what it is doing. I'm not sure what locks that query needs to hold, it may be blocked by any concurrent query from a user in that group, I dunno. Whould those locks go away by themselves in time? Yeah, once the transactions holding the locks complete. What was consuming all the CPU? I assume from what you wrote previously that it is a postgres backend. If so, what was it doing? ps output (just one backend) postgres 4759 0.0 2.0 19368 5292 ?S21:02 0:00 postgres: czyn_738773 czyn 127.0.0.1(55431) UPDATE waiting --- "czyn" is the name of the database, "czyn_738773" is database username. IP address is localhost, since the database purpose is web serbvice back-end. If the update is waiting, it is blocked and should not be consuming any noticeable cpu. This was using lots of CPU at this moment? Still, I'm not sure what that UPDATE counld that be. You can use ps, the pg_stat_activity view and strace to figure that out. - exerpt from pg_stat_activity- 17660 | czyn |4759 |19541 | czyn_738773 | | | 2006-09-27 21:02:05.914728+02 | 127.0.0.1 | 55431 (18 rows) -- But this is not very meaningfull to me. You need to add this to your postgresql.conf: stats_command_string = on Then send a HUP signal to the postmaster (you can use pgctl reload for this or 'kill -HUP') Then you can see what the exact command is. Another option that may be helpful to diagnose is: log_min_duration_statement = 500 This will output the sql for all commands taking longer than 500ms. You can adjust this up and down depending on what counts as "slow" in your app. You probably only want it on temporarily, but I find it sometimes helpful to always leave it on with a suitably high value (like 1000). That way you can always troll the logs for laggards. ...another postgress-process restart, and another VACCUM ANALYSE, this one completes in 3-5 secs. Now "ALTER GROUP ..." goes OK. And everything seams working. Was something else using the database previously? Was something else different this time than the last? No. My impression was (naive, I know), that not all things were correctly cleand-up on the first restart. Now I think, that on the second go I was just a little bit more lucky - www service was calm for a little longer. That was anough for VACUUM to complete. Yeah, it sounds like things are fairly busy given the locks above. Still, throuble started later. How big is the data on disk? Is it possible that you have queries close to nothing: c.a. 200MiB Sounds like the problem is more about concurrency then data size. How many concurrent connections do you have (select count(*) from pg_stat_activity)? How many are in transaction and running queries concurrently? If you see multiple instances of a given query (or the same one appearing frequently), try running EXPLAIN ANALYZE on it. That'll tell you what makes it slow. problem (hardware, os, fs). When you saw this happen did you happen to check if it was user or system cpu usage? I had a corrupt It's the user CPU time. Yeah, just sounds like a scaling issue. Hopefully easily solved ;^) As it currently looks like some unknown UPDATE causes the trouble, how can I identify what it is? see config recommendations above. 1. the database is not very sophisticated, apart from what I've mentioned earlier, it does have just a few stored procedures in pgsql - nothing fancy. And there is no triggers just specialized views (although quite a lot of referencial integrity constrains). These can be a source of lock contention on updates (though this is much better on 8.1 compared to 7.4). Once you know the update query that's getting blocked you can know for sure. 2. machine is 'from the old days': 450MHz CPU What is the iowait like on this box? You can use iostat to determine this. That will tell you if the disks are also struggling. 3. the system is currently under 'available-for-public' condition, so there may be 'waves' (peeks) of activity. 4. when the system was tested in-lab (small number of users - no risk / not-tested for havier load behavior), I haven't noticed this 'blocking'. With any luck the above will help point out some su
Re: [GENERAL] How to Examine a view
is this what you mean? testdb=# \d foo_view View "public.foo_view" Column | Type | Modifiers +---+--- x | character varying(20) | stuff | text | y | character varying(20) | View definition: SELECT a.x, (a.x::text || ' :'::text) || substr(b.y::text, 1, 10) AS stuff, b.y FROM foo1 a, foo2 b; On Mon, Sep 25, 2006 at 08:44:33AM -0700, [EMAIL PROTECTED] wrote: > I have setup a view consisting of two tables and some compound fields. > Now I would like from the client perspective see what those compound > fields actually are originally. > > Let's (for simplicity I have inlcuded only one table) say we have: > CREATE OR REPLACE VIEW clientview AS > SELECT c.clientid, (c.orderer_name::text || ' :'::text) || > substr(c.occasion::text, 1, 10) AS "Order occasion", c.customerid >FROM client c > > Is it possible to query postgres to tell me what field names and field > types "Order occasion" really consists of? > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [HACKERS] PostgreSQL HA questions
First, I'm moving this to -general, because this is way off topic for -hackers as near as I can tell. On Tue, Sep 26, 2006 at 10:39:18PM +0200, Dragan Zubac wrote: > 1. Is it possible for multiply PostgreSQL instances (engines,cores) to use > same DATA space? No. In fact, this is a very good way to cause corruption. What you _can_ do is set up a "watchdog" process that allows a different machine to take over the filesystem on a shared disk array, for instance, and come back up in recovery mode. So your outage is roughly as long as the time to notice your primary node failed, plus the time to recover from database crash. There are various software packages that will allow you to do this. NOT ALL OF THEM WORK WELL. Go back and read that sentence again. No, I am not saying this because of any painful experiences I have ever had ;-) > 2. Becouse of vaccuming issues,is it possible to create such a client > process which will use two identical tables,and on receiving a signal,it > will switch between those tables.For example,first a client application uses > table1,after some time,send a signal to process,it will switch using > table2,so You can freely vacuum table1 or whatsoever.After vacuuming > done,table 1 will sinchronize with table2 and keep up-to-date until You send > next signal to application,which will switch using table1,so You can vacuum > table2. It isn't clear to me why you think you need to do this: vacuum doesn't block your queries anyway. If the idea is that you have a table that you'd rather TRUNCATE and not have to vacuum, however, that makes sense. There are several strategies for this. My colleague Chris Browne seems really to like this kind of functionality, and has discussed it more than once on the -general list. I think you can find his detailed outlines of how to do this sort of thing by searching for "rotor tables". A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Cumulative aggregate
"Paolo Saudin" <[EMAIL PROTECTED]> writes: > -- function for float (NOT WORKING) > create function myfloat4_sum(float4,float4) returns float4 > as 'select float4pl($1,$2)::float4;' > language SQL; > -- aggregate > create aggregate myagg2_sum (basetype = float4, sfunc = myfloat4_sum, stype > = float4); > -- result > select myagg2_sum(fld2) from mytest; --> NOTHING ??? INSTEAD OF 6.73 > What am I doing wrong ?? (1) not marking the transition function as strict --- this affects the behavior of the aggregate; (2) not providing an initial condition. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] cyclical redundancy checksum algorithm(s)?
"Karen Hill" <[EMAIL PROTECTED]> writes: > Ralph Kimball states that this is a way to check for changes. You just > have an extra column for the crc checksum. When you go to update data, > generate a crc checksum and compare it to the one in the crc column. > If they are same, your data has not changed. You sure that's actually what he said? A change in CRC proves the data changed, but lack of a change does not prove it didn't. People do sometimes use this logic in connection with much wider "summary" functions, such as an MD5 hash. I wouldn't trust it at all with a 32-bit CRC, and not much with a 64-bit CRC. Too much risk of collision. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] cyclical redundancy checksum algorithm(s)?
Gene Wirchenko wrote: > >I just finished reading one of Ralph Kimball's books. In it he > >mentions something called a cyclical redundancy checksum (crc) > >function. A crc function is a hash function that generates a checksum. > > > >I am wondering a few things. A crc function would be extremely useful > >and time saving in determining if a row needs to be updated or not (are > >the values the same, if yes don't update, if not update). In fact > >Ralph Kimball states that this is a way to check for changes. You just > >have an extra column for the crc checksum. When you go to update data, > >generate a crc checksum and compare it to the one in the crc column. > >If they are same, your data has not changed. > > > >Yet what happens if there is a collision of the checksum for a row? > > Then you get told that no change has occurred when one has. I > would call this an error. That's exactly what I thought when I read that in his book. I was thinking back to the sha1 and md5 algorithms, maybe a special crc algorithm is safe from this. > >Ralph Kimball did not mention which algorithm to use, nor how to create > >a crc function that would not have collisions. He does have a PhD, > >and a leader in the OLAP datawarehouse world, so I assume there is a > >good solution. > > Your error. Having a Ph.D. does not stop someone from being > wrong. > >Is there a crc function in postgresql? If not what algorithm would I > >need to use to create one in pl/pgsql? > > I think you are focusing on irrelevant minutiae. Is the > performance really that bad that you have go to odd lengths to up it? It is not for performance. It is to save time writing a lot of stored procedure code. when you hav e an updateable view with 70 values that need to be checked for changes a checksum starts to sound very appealing. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] memory issues when running with mod_perl
Someone posted an issue to the mod-perl list a few weeks ago about their machine losing a ton of memory under a mod-perl2/apache/ postgres system - and only being able to reclaim it from reboots A few weeks later I ran into some memory related problems, and noticed a similar issue. Starting / stopping the clients had no effect on memory (which was expected... the new ones just pulled in from the shared cache ) But stopping the daemon didn't affect memory either. running ipcs, i saw the shared memory lock freed. but the kernel never seemed to get it back. and then I'd run into swap. I felt this on freebsd 6.x + pg 8.1.x , seperate people on the list had it under the 2.4 / 2.6 kernels with 7.x and 8.x pgs. someone just posted that they have the same issue on one machine under 7.4.9, but not (yet) under 7.4.13 does anyone have some suggestions on how to test this to make sure its a pg issue ? it often takes a few days for pg to consume enough memory for this behavior to set in place. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] cyclical redundancy checksum algorithm(s)?
In response to Tom Lane <[EMAIL PROTECTED]>: > "Karen Hill" <[EMAIL PROTECTED]> writes: > > Ralph Kimball states that this is a way to check for changes. You just > > have an extra column for the crc checksum. When you go to update data, > > generate a crc checksum and compare it to the one in the crc column. > > If they are same, your data has not changed. > > You sure that's actually what he said? A change in CRC proves the data > changed, but lack of a change does not prove it didn't. > > People do sometimes use this logic in connection with much wider > "summary" functions, such as an MD5 hash. I wouldn't trust it at all > with a 32-bit CRC, and not much with a 64-bit CRC. Too much risk of > collision. I remember reading some high-level stuff on rsync and how it uses multiple checksums in tandem. Don't know the math, though -- if both the SHA256 and the MD5 match, what are the chances that it's changed? You also hit diminishing returns ... after calculating so many checksums, you might be better off just checking the data itself, unless that data is very large. -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] change the order of FROM selection to make query work
Scott Marlowe <[EMAIL PROTECTED]> writes: > On Tue, 2006-09-26 at 09:19, Tom Lane wrote: >> I was aware that MySQL parses this sort of structure wrongly, but it's >> disappointing to hear that sqlite does too :-( > And I think MySQL fixed this abberant behaviour in the newest beta. Actually they seem to have fixed it awhile ago --- I get this in 5.0.22: mysql> create table t1(f1 int); Query OK, 0 rows affected (0.02 sec) mysql> create table t2(f2 int); Query OK, 0 rows affected (0.01 sec) mysql> select * from t1 c, t2 a join t1 b on (a.f2 = b.f1); Empty set (0.00 sec) mysql> select * from t1 c, t2 a join t1 b on (a.f2 = c.f1); ERROR 1054 (42S22): Unknown column 'c.f1' in 'on clause' mysql> select * from t1 c cross join t2 a join t1 b on (a.f2 = c.f1); Empty set (0.01 sec) which appears to conform to the letter of the standard. FYI, PG 8.2 will produce a possibly more useful error message: regression=# select * from t1 c, t2 a join t1 b on (a.f2 = c.f1); ERROR: invalid reference to FROM-clause entry for table "c" LINE 1: select * from t1 c, t2 a join t1 b on (a.f2 = c.f1); ^ HINT: There is an entry for table "c", but it cannot be referenced from this part of the query. regression=# regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] cyclical redundancy checksum algorithm(s)?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/27/06 16:02, Karen Hill wrote: > Gene Wirchenko wrote: [snip] >>> Yet what happens if there is a collision of the checksum for a row? >> Then you get told that no change has occurred when one has. I >> would call this an error. > > That's exactly what I thought when I read that in his book. I was > thinking back to the sha1 and md5 algorithms, maybe a special crc > algorithm is safe from this. I doubt it. The typical CRC is 32 bits, whereas the MD5 hash is 128 bits and SHA1 is 160 bits. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD4DBQFFGuxUS9HxQb37XmcRAr0GAJ0d6r4C7Zt2tug/AHH/aCPG5p8UMgCY8bDU MgTQuPe9uNT5Ny3nW0qk6w== =o//D -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 'pg_ctl -w' times out when unix_socket_directory is set
When I have "unix_socket_directory" set to an alternate value, "pg_ctl - D data -w start" times out. If I set it to default, it works fine. I'm using postgresql 8.1.4 on FreeBSD. Perhaps pg_ctl is waiting to see the socket file in /tmp/ before reporting that postgresql successfully started? Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] cyclical redundancy checksum algorithm(s)?
Tom Lane wrote: > "Karen Hill" <[EMAIL PROTECTED]> writes: > > Ralph Kimball states that this is a way to check for changes. You just > > have an extra column for the crc checksum. When you go to update data, > > generate a crc checksum and compare it to the one in the crc column. > > If they are same, your data has not changed. > > You sure that's actually what he said? A change in CRC proves the data > changed, but lack of a change does not prove it didn't. On page 100 in the book, "The Data Warehouse Toolkit" Second Edition, Ralph Kimball writes the following: "Rather than checking each field to see if something has changed, we instead compute a checksum for the entire row all at once. A cyclic redundancy checksum (CRC) algorithm helps us quickly recognize that a wide messy row has changed without looking at each of its constituent fields." On page 360 he writes: "To quickly determine if rows have changed, we rely on a cyclic redundancy checksum (CRC) algorithm. If the CRC is identical for the extracted record and the most recent row in the master table, then we ignore the extracted record. We don't need to check every column to be certain that the two rows match exactly." > > People do sometimes use this logic in connection with much wider > "summary" functions, such as an MD5 hash. I wouldn't trust it at all > with a 32-bit CRC, and not much with a 64-bit CRC. Too much risk of > collision. > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 'pg_ctl -w' times out when unix_socket_directory is
On Wed, 2006-09-27 at 14:26 -0700, Jeff Davis wrote:
> When I have "unix_socket_directory" set to an alternate value, "pg_ctl -
> D data -w start" times out. If I set it to default, it works fine.
>
> I'm using postgresql 8.1.4 on FreeBSD.
>
> Perhaps pg_ctl is waiting to see the socket file in /tmp/ before
> reporting that postgresql successfully started?
>
I took a look at the source quickly (as usual, the postgres source is so
easy to read I should have looked before I posted) and I found that the
problem seems to be in test_postmaster_connection() in pg_ctl.c.
The function checks for a non-default port, including scanning the
configuration file, but does not look for a non-default socket
directory.
It seems reasonable to add a check to find the real socket directory
before trying the connection.
I have attached a patch. I wrote it very quickly, but it seems to work
as I expect.
Regards,
Jeff Davis
diff -ru pgsql.orig/src/bin/pg_ctl/pg_ctl.c pgsql/src/bin/pg_ctl/pg_ctl.c
--- pgsql.orig/src/bin/pg_ctl/pg_ctl.c 2006-09-27 14:57:20.0 -0700
+++ pgsql/src/bin/pg_ctl/pg_ctl.c 2006-09-27 14:58:02.0 -0700
@@ -387,9 +387,13 @@
bool success = false;
int i;
char portstr[32];
+ char sockdirstr[256];
+ char *loginhost;
char *p;
*portstr = '\0';
+ *sockdirstr = '\0';
+ loginhost = NULL;
/* post_opts */
for (p = post_opts; *p;)
@@ -443,6 +447,37 @@
}
}
}
+ if (!*sockdirstr)
+ {
+ char **optlines;
+
+ optlines = readfile(conf_file);
+ if (optlines != NULL)
+ {
+ for (; *optlines != NULL; optlines++)
+ {
+p = *optlines;
+
+while (isspace((unsigned char) *p))
+ p++;
+if (strncmp(p, "unix_socket_directory", strlen("unix_socket_directory")) != 0)
+ continue;
+p += strlen("unix_socket_directory");
+while (isspace((unsigned char) *p))
+ p++;
+if (*p != '=')
+ continue;
+p++;
+while (isspace((unsigned char) *p))
+ p++;
+p++; /* skip first single quote */
+StrNCpy(sockdirstr, p, Min(strcspn(p, "'") + 1,
+ sizeof(sockdirstr)));
+/* keep looking, maybe there is another */
+ }
+ }
+ loginhost = sockdirstr;
+ }
/* environment */
if (!*portstr && getenv("PGPORT") != NULL)
@@ -454,7 +489,7 @@
for (i = 0; i < wait_seconds; i++)
{
- if ((conn = PQsetdbLogin(NULL, portstr, NULL, NULL,
+ if ((conn = PQsetdbLogin(loginhost, portstr, NULL, NULL,
"postgres", NULL, NULL)) != NULL &&
(PQstatus(conn) == CONNECTION_OK ||
(strcmp(PQerrorMessage(conn),
Only in pgsql/src/bin/pg_ctl: pg_ctl.c.orig
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Solution for rolling back many transactions?
Phillip Tornroth wrote: In case it's useful information, the test database is pretty small... Maybe 15 or 20 megs. The unit tests are individually pretty small.. Less than 100 inserts each, for sure... So there's not that much to 'roll back'.. As far as using transactions to undo the state of the database, let me know if that's possible.. All of our tests have their own transaction boundaries that they're applying. I don't want to undo that, but certainly if I could nest each test in a longer-running tx and then roll it back.. that could work. I use savepoints for my test framework. Within a single transaction create savepoints and run your tests. When you are done, simply rollback the entire transaction. Within your tests, you can commit or rollback individual savepoints. http://www.postgresql.org/docs/current/static/sql-savepoint.html Regards, LL ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pl/perl autonomous transactions question
On Monday 25 September 2006 15:05, Bob wrote:
> I would like to use autonomous transactions for a large batch process and I
> want this all encapsulated within stored procedures. I want to commit after
> say every 15,000 records. The only way I have found to do this is to use
> the perl DBI in my stored procedure to establish a new connection to the
> database.
>
> 1. Is
> there any way to tell the DBI connection to use the current credtials
> just with a new connection?
>
I guess the depends on where you draw the line between credentials and
connection information.
> 2. Is there any way to get the spi call to create a new connection instead
> of using the connection it is called with?
>
no... that is after all the whole point of spi.
> One issue I see with my current DBI solution is
> I need to hard code or pass in as variables the connection
> information. I would prefer not to have the password lying around in
> plain site. Keep in mind this is a batch process not a something I
> that is called manually where a user is
> going to be entering their username and password in.
>
One option is to use dbi-link, which will store the connection information in
it's own table, so you only pass in a reference to the particular connection
inside your function. You can then control permissions on this table more
granularly. Taking that a step farther would be creating specific functions
to handle the posting of the autonomous transactions and then calling those
inside your functions.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Replication and PITR
On Monday 25 September 2006 07:48, Bo Lorentsen wrote:
> Jeff Davis wrote:
> > Standby mode means that the database is kept almost up to date with the
> > master, but is not "up". When the master goes down, you can bring the
> > standby machine up. Until then, you unfortunately can't even do read
> > queries on that machine.
>
> Do you know if this will change in the future ?
>
> > If you want more of a master/slave setup for performance, you should
> > take a second look at Slony. A PITR standby doesn't help you with
> > performance at all.
>
> Ok, I can see that ... so PITR is for a standby backup DB, with at the
> best ... manual fail over ?
>
> > Why don't you like the fact that Slony is trigger-based? Does that cause
> > you a problem?
>
> Hmm, well i guess i dislike the idea of having a high level mechanism to
> collect data, not a rational argument, sorry. The PITR just seemed so
> right as it has a more prober low level approach, but it sound to me
> like Slony is the only real choice at the moment, and it will do the job
> with a relatively low overhead.
>
> > And missing DDL is mainly a problem when you want to provide postgresql
> > to many people, and you have no idea how they will use it. If that's the
> > case, standby PITR might be a better solution for you. Slony has nice
> > "execute script" functionality that is useful for making DDL changes on
> > all machines.
>
> Ok, I think that the only thing I really need to do is to try to work
> more with Slony and learn to understand it. And the DDL problem is more
> when others need to maintain the system, and I then have to explain how
> to do this and that, and I think I am a bit spoiled by the easy working
> of the mysql replication :-)
>
Hmm almost sounds like what you really want is mammoth replicator... lower
level than slony, built into the db, can handle ddl (iirc) not oss
though.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] text to point conversion not working. ( cannot cast type text to point )
Hi all,
Using pgsql 8.0.1
I'm just starting with using the geometry data types in postgres, and ran into
what seems like a very basic problem. Namely, I can't seem to convert/cast
type text into type point when that text results from any expression. Ie, it
*only* works for a plain string literal.
Examples:
select '1,2'::point;
point
---
(1,2)
That works with a string literal. This does not.
select ('1' || ',2')::point;
ERROR: cannot cast type text to point
Nor does this.
select cast('1' || ',2' as point);
ERROR: cannot cast type text to point
Nor this.
select '1,2'::varchar::point;
ERROR: cannot cast type character varying to point
Nor this.
select '1,2'::char::point;
ERROR: cannot cast type character to point
This works. With a string literal.
select point_in('1,2');
point_in
--
(1,2)
But this does not. :(
select point_in('1' || ',2');
ERROR: function point_in(text) does not exist
So, is there a built-in way to do this, or.?
Background:
I have a hierarchical table where I have coordinate data for only the leaf
nodes. I therefore want to find the center of all the leaf nodes under a
given parent node, and set the parent node coordinate to that center point.
I can calcululate that center point using aggregate functions (min, max) to
find the necessary x,y values.So my query would look something like this:
update parent_table set col =
(select (max(pnt[0])-min(pnt[0]))/2+min(pnt[0]) || ',' ||
max(pnt[1])-max(pnt[1])/2+min(pnt[1]) from point_tmp where condition)
where condition2 ;
Where point_tmp.tmp is defined as a point column.
However, when I try to do it, I get a similar error:
column "col" is of type point but expression is of type text
If the above task can be performed some other way, perhaps I don't require
string concatenation
--
Dan Libby
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Documenting stored procedures and functions
Jim Nasby <[EMAIL PROTECTED]> writes: > Take a look at http://pgfoundry.org/projects/autodoc/. I believe it uses > comments (ie: COMMENT ON) as well, so you can get some info into that. Hi Jim! Thanks for pointing me to the tool. The correct link to it is http://www.rbt.ca/autodoc/. It might fit my needs, with some work done with comments. :-) -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] continued segmentation fault
Is there any reason can't update to a newer version. Like 8.x? Geoffrey wrote: > We continue to have segmentation faults of the /usr/bin/postgres process > as I mentioned in an earlier thread. In all cases, the core file > always indicates a segmentation fault, but the backtraces don't seem to > consistently point to any particular problem. Then again, when you go > stomping around in memory where you don't belong, all bets are probably off. > > I'm wondering out loud (here) if anyone thinks it might have something > to do with the version we're on? 7.4.7. We are planning to upgrade to > 7.4.13 soon, but were hoping to address this existing issue first. > > It's a bit difficult debugging this issue as I must initiate gdb via the > client. I have yet to reproduce this problem in my test environment, > but then again, I'm running my single debugging client, whereas in the > production system, there could well be 150-200 clients running. > > I've attached the latest backtrace, in the event anyone sees anything > glaringly obvious, please slap me in the head... > > Thanks in advance. > > -- > Until later, Geoffrey > > Those who would give up essential Liberty, to purchase a little > temporary Safety, deserve neither Liberty nor Safety. > - Benjamin Franklin > > --020607020903010406060907 > Content-Type: text/plain; charset=utf-8 > Content-Disposition: inline; > filename="bt1031" > X-Google-AttachSize: 5397 > > Using host libthread_db library "/lib/tls/libthread_db.so.1". > Core was generated by `postgres: chuck sev [local] SELECT'. > Program terminated with signal 11, Segmentation fault. > #0 0x0815c973 in cmtreefree (cm=0x9925064, tree=0x0, level=2) > at regc_color.c:125 > in regc_color.c > #0 0x0815c973 in cmtreefree (cm=0x9925064, tree=0x0, level=2) > at regc_color.c:125 > #1 0x0815c9bc in cmtreefree (cm=0x9925064, tree=0x98a1528, level=1) > at regc_color.c:131 > #2 0x0815c9bc in cmtreefree (cm=0x9925064, tree=0x9925144, level=0) > at regc_color.c:131 > #3 0x0815c8f1 in freecm (cm=0x9925064) at regc_color.c:97 > #4 0x0815b458 in rfree (re=0x800) at regcomp.c:2105 > #5 0x08162825 in pg_regfree (re=0x9925064) at regfree.c:53 > #6 0x081aa8ad in RE_compile_and_execute (text_re=0x9f171a0, > dat=0x800 , dat_len=170796744, cflags=11, > nmatch=0, pmatch=0x0) at regexp.c:212 > #7 0x081aafd2 in texticregexne (fcinfo=0xfeffac80) at regexp.c:387 > #8 0x08107634 in ExecMakeFunctionResult (fcache=0x9f54ff0, > econtext=0x9f548e8, isNull=0xfeffad9b "", isDone=0x0) at execQual.c:907 > #9 0x081091a0 in ExecEvalExpr (expression=0x9f54ff0, econtext=0x9f548e8, > isNull=0x0, isDone=0x9925064) at execQual.c:2257 > #10 0x08109dbb in ExecQual (qual=0x9f54b10, econtext=0x9f548e8, > resultForNull=0 '\0') at execQual.c:2859 > #11 0x0810a1e1 in ExecScan (node=0x9f54860, accessMtd=0x8112150 ) > at execScan.c:129 > #12 0x08112239 in ExecSeqScan (node=0x800) at nodeSeqscan.c:131 > #13 0x081060d6 in ExecProcNode (node=0x9f54860) at execProcnode.c:306 > #14 0x0810e13b in ExecHash (node=0x9f54730) at nodeHash.c:81 > #15 0x081061b5 in ExecProcNode (node=0x9f54730) at execProcnode.c:364 > #16 0x0810ec6c in ExecHashJoin (node=0x942bbc8) at nodeHashjoin.c:128 > #17 0x08106167 in ExecProcNode (node=0x942bbc8) at execProcnode.c:337 > #18 0x0810f03b in ExecHashJoinOuterGetTuple (node=0x800, hjstate=0x942b460) > at nodeHashjoin.c:510 > #19 0x0810ea94 in ExecHashJoin (node=0x942b460) at nodeHashjoin.c:152 > #20 0x08106167 in ExecProcNode (node=0x942b460) at execProcnode.c:337 > #21 0x0810d288 in agg_fill_hash_table (aggstate=0x942b850) at nodeAgg.c:905 > #22 0x0810cec7 in ExecAgg (node=0x942b850) at nodeAgg.c:654 > #23 0x0810619b in ExecProcNode (node=0x942b850) at execProcnode.c:356 > #24 0x08104a1d in ExecutePlan (estate=0x942b398, planstate=0x942b850, > operation=CMD_SELECT, numberTuples=10, direction=2048, dest=0x826d134) > at execMain.c:1100 > #25 0x08103df8 in ExecutorRun (queryDesc=0x9f17848, > direction=ForwardScanDirection, count=2048) at execMain.c:249 > #26 0x0817b07b in PortalRunSelect (portal=0x9439c98, forward=0 '\0', count=10, > dest=0x826d134) at pquery.c:590 > #27 0x0817b6d3 in PortalRunFetch (portal=0x9439c98, fdirection=2048, > count=2048, dest=0x800) at pquery.c:961 > #28 0x08117c67 in _SPI_cursor_operation (portal=0x9439c98, forward=1 '\001', > count=10, dest=0x826d134) at spi.c:1315 > #29 0x081171eb in SPI_cursor_fetch (portal=0x800, forward=1 '\001', > count=2048) > at spi.c:881 > #30 0x00a0ca39 in exec_stmt_fors (estate=0xfeffb210, stmt=0x950b188) > at pl_exec.c:1391 > #31 0x00a0c10e in exec_stmt (estate=0xfeffb210, stmt=0x950b188) > at pl_exec.c:963 > #32 0x00a0c005 in exec_stmts (estate=0xfeffb210, stmts=0x96fc0d8) > at pl_exec.c:903 > #33 0x00a0be15 in exec_stmt_block (estate=0xfeffb210, block=0x944ba68) > at pl_exec.c:859 > #34 0x00a0b061 in plpgsql_exec_function (func=0x96069c8,
Re: [GENERAL] dbi-link questions + patch
On Wed, Sep 27, 2006 at 03:50:46PM +0200, Filip RembiaÅkowski wrote:
> Hi all :)
>
> first, sorry for crossposting but dbilink mailinglist is extremely low traffic
> so I decided to mail this also to pgsql-general
>
> I'm looking for a solution that gives PostgreSQL "remote server" aka "proxy
> tables" functionality.
DBI-Link should do that.
> We're trying here to evaluate dbi-link, and have some problems.
> I got version 1.0.0 from PgFoundry.
>
> First I had to do some fixes to make it install on PostgreSQL 8.1 with Perl
> 5.8.8 (the patch is below).
> but then I also had some problems.
>
> question 1)
>
> -- PREPARATION (stripped output/diagnostic messages):
> pgdba=# CREATE DATABASE local;
> pgdba=# CREATE DATABASE remote;
> pgdba=# \c remote
> remote=# CREATE TABLE tab1(id bigserial, data text);
> remote=# insert into tab1(data) values('AAA');
> remote=# \c local
> local=# CREATE LANGUAGE 'plperlu';
> local=# \i dbi_link.sql
> local=# SELECT dbi_link.make_accessor_functions(
> local-# 'dbi:Pg:dbname=remote;host=localhost;port=5810', 'pgdba',
> local-# NULL, '{AutoCommit => 1, RaiseError => 1}', 'public', NULL, remote');
>
> -- TEST 1
> local=# select * from remote.tab1;
> NOTICE: Connected to database
> NOTICE: sql is
> COMMENT ON COLUMN tab1_rowtype.id IS 23361
> bigint
> 23361
>
> id | data
> +--
> 1 | AAA
> (1 row)
> - it is OK but... why these NOTICEs? is it normal behaviour?
They're debugging info. You can remove them.
> -- TEST 2
> local=# insert into remote.tab1(data) values('BBB');
> ERROR: error from Perl trigger function: column "ad" does not exist at line
> 28.
> - it is definitely not OK. what could be the problem here?
>
>
> question 2)
> why is DBI-Link marked as "stable" on PgFoundry?
> if it does not even work out-of-the-box, it is not mature yet.
> maybe it should be "beta"?
I think it's bitrotted :P
> question 3)
> did anyone try to establish DBI-Link between UTF8 encoded database and
> non-UTF8
> encoded database? i'm asking because i got:
> utf8db=# select count(*) from remotelatin2db.dict01;
> (...)
> ERROR: invalid UTF-8 byte sequence detected near byte 0xf3
Nope, but if you have something you can put together as a regression
test, I'd be grateful.
> question 4) could anyone please suggest some software that gives similar
> functionality (and works)?
That's about what there is. I'm working on 2.0, which has a lot of
improvements including more tests.
Cheers,
D
>
>
>
> regards && thanks for your time
> Filip
>
>
>
>
> dbi-link-1.0.0 patch begin.
>
>
>
> diff -Naur dbi-link-1.0.0/make_connection.sql
> dbi-link-1.0.0.1/make_connection.sql
> --- dbi-link-1.0.0/make_connection.sql 2005-01-26 09:47:11.0 +0100
> +++ dbi-link-1.0.0.1/make_connection.sql2006-09-27 13:12:14.0
> +0200
> @@ -36,7 +36,7 @@
> , db_password => $db_password
> );
>
> -return TRUE;
> +return 'TRUE';
>
> sub check_connection {
> my %parms = (
> diff -Naur dbi-link-1.0.0/remote_query.sql dbi-link-1.0.0.1/remote_query.sql
> --- dbi-link-1.0.0/remote_query.sql 2005-01-26 09:47:11.0 +0100
> +++ dbi-link-1.0.0.1/remote_query.sql 2006-09-27 13:15:12.0 +0200
> @@ -164,7 +164,7 @@
> FROM dbi_link.dbi_connection
> WHERE ad = $data_source_id
> SQL
> -my ($data_source, $user, $auth, $dbh_attr);
> +my ($data_source, $user_name, $auth, $dbh_attr);
> my $driver_there = spi_exec_query($dtsql);
> my $nrows = $driver_there->{processed};
> if ($nrows == 0) {
> @@ -260,6 +260,8 @@
> RETURNS TRIGGER
> LANGUAGE plperlu
> AS $$
> +our %_TD;
> +my $user_name;
> #
> # #
> # Immediately reject anything that's not an INSERT. #
> @@ -371,7 +373,7 @@
> INSERT INTO $table (
>@{[join("\n, ", sort keys %$new) ]}
> ) VALUES (
> - @{[join("\n, ", { $new->{$_} } sort keys %$new) ]}
> + @{[join("\n, ", map { $new->{$_} } sort keys %$new) ]}
> )
> SQL
> my $sth = $dbh->prepare($sql);
> @@ -379,7 +381,7 @@
> }
>
> sub update {
> -my $table = $_TD{relname}
> +my $table = $_TD{relname};
> my $sql = < UPDATE $table
> SET
> @@ -400,7 +402,7 @@
> }
>
> sub delete {
> -my $table = $_TD{relname}
> +my $table = $_TD{relname};
> my $sql = < DELETE FROM $table
> WHERE
>
>
>
> dbi-link-1.0.0 patch end.
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] grant select on all tables of schema or database
It seems like it should be a very easy problem to solve I just need one role to have select privileges on all the tables of a particular schema or database including any new tables that are created since they are created programmatically daily. I've combed google and the docs to no avail. Do I need to write pg/plsql scripts just to so something like that?? I believe on other dbms you can just say grant all on schema.* or something to that effect. The script i found below would be ok if tables weren't being created constantly.
using version 8.1.4thanks,GeneCREATE OR REPLACE FUNCTION pgx_grant(text, text, text) RETURNS int4 AS$BODY$DECLARE priv ALIAS FOR $1; patt ALIAS FOR $2; user ALIAS FOR $3;
obj record; num integer;BEGIN num:=0; FOR obj IN SELECT relname FROM pg_class WHERE relname LIKE patt || '%' AND relkind in ('r','v','S') LOOP EXECUTE 'GRANT ' || priv || ' ON ' ||
obj.relname || ' TO ' || user; num := num + 1; END LOOP; RETURN num;END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;ALTER FUNCTION pgx_grant(text, text, text) OWNER TO root;
Re: [GENERAL] cyclical redundancy checksum algorithm(s)?
Stepping back a bit... Why not use an update trigger on the affected tables to record a lastupdated timestamp value when the record is changed. Surely this is simpler thanks computing some kind of row hash? John Karen Hill wrote: Tom Lane wrote: "Karen Hill" <[EMAIL PROTECTED]> writes: Ralph Kimball states that this is a way to check for changes. You just have an extra column for the crc checksum. When you go to update data, generate a crc checksum and compare it to the one in the crc column. If they are same, your data has not changed. You sure that's actually what he said? A change in CRC proves the data changed, but lack of a change does not prove it didn't. On page 100 in the book, "The Data Warehouse Toolkit" Second Edition, Ralph Kimball writes the following: "Rather than checking each field to see if something has changed, we instead compute a checksum for the entire row all at once. A cyclic redundancy checksum (CRC) algorithm helps us quickly recognize that a wide messy row has changed without looking at each of its constituent fields." On page 360 he writes: "To quickly determine if rows have changed, we rely on a cyclic redundancy checksum (CRC) algorithm. If the CRC is identical for the extracted record and the most recent row in the master table, then we ignore the extracted record. We don't need to check every column to be certain that the two rows match exactly." People do sometimes use this logic in connection with much wider "summary" functions, such as an MD5 hash. I wouldn't trust it at all with a 32-bit CRC, and not much with a 64-bit CRC. Too much risk of collision. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] grant select on all tables of schema or database
when u connect to the database type:\h GRANTand you will get all the Grant options:GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]This will grant the privileges on all tables under the database HTHGene <[EMAIL PROTECTED]> wrote: It seems like it should be a very easy problem to solve I just need one role to have select privileges on all the tables of a particular schema or database including any new tables that are created since they are created programmatically daily. I've combed google and the docs to no avail. Do I need to write pg/plsql scripts just to so something like that?? I believe on other dbms you can just
say grant all on schema.* or something to that effect. The script i found below would be ok if tables weren't being created constantly. using version 8.1.4thanks,GeneCREATE OR REPLACE FUNCTION pgx_grant(text, text, text) RETURNS int4 AS$BODY$DECLARE priv ALIAS FOR $1; patt ALIAS FOR $2; user ALIAS FOR $3; obj record; num integer;BEGIN num:=0; FOR obj IN SELECT relname FROM pg_class WHERE relname LIKE patt || '%' AND relkind in ('r','v','S') LOOP EXECUTE 'GRANT ' || priv || ' ON ' || obj.relname || ' TO ' || user; num := num + 1; END LOOP; RETURN num;END;$BODY$ LANGUAGE 'plpgsql' VOLATILE;ALTER FUNCTION pgx_grant(text, text, text) OWNER TO root;
Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.
