Re: [GENERAL] Are indexes used with LIKE?
Thank you for the answer! Sure, the possiblity of having a separate column for each flag was considered, but a common columnn is preferred -- I do not remember exactly why. (I do not directly make that decision.) I guess the main reason is that adding new columns to the table complicates the upgrade procedure with our existing customer base. Thank you again! Peter Michael Fuhr wrote: On Mon, Jan 23, 2006 at 08:00:01PM +0100, Kovcs Pter wrote: Are indexes on VARCHAR columns used with the LIKE operator, and if so, how efficiently are they used? I can imagine that using indexes can be easy with the starting literal characters up to the first percent sign such as in: LIKE 'ZOE%QQWE%' But, after the first % sign, things can get more difficult. The planner can use an index on the starting literal characters; how difficult the query becomes after that depends on how discriminating those initial characters are. If values matching the initial characters comprise a small fraction of the table then the query will probably use an index and be fast, but if they comprise a large fraction of the table, or if the search string starts with a wildcard, then you'll get a sequential scan, which might be slow. The reason I am asking is that we are thinking about discriminating between rows of a table based on a VARCHAR column containing various one-character flags. We could then use the LIKE operator for formulating filter conditions. Have you considered putting each flag in a separate column and indexing those columns? If you're using 8.1 the planner would probably use bitmap index scans and come up with a fast plan regardless of which columns you restrict on. And performance issues aside, some people would consider that a better design. However, a disadvantage might be that your queries would be more complex. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Shared Database across multiple servers using OCFS2
Hi there, Does anyone know if it is possible to share a single database across multiple machines each running a database server using OCFS2? Thanks in advance, Kind regards, Henry ___ The information contained in this e-mail is confidential and may contain proprietary information. It is meant solely for the intended recipient. Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted in reliance on this, is prohibited and may be unlawful .No liability or responsibility is accepted if information or data is, for whatever reason corrupted or does not reach its intended recipient. No warranty is given that this e-mail is free of viruses. The views expressed in this e-mail are, unless otherwise stated, those of the author and not those of FirstRand Bank Limited or its management. FirstRand Bank Limited reserves the right to monitor, intercept and block e-mails addressed to its users or take any other action in accordance with its e-mail use policy. Licensed divisions of FirstRand Bank Limited are authorised financial service providers in terms of the Financial Advisory and Intermediary Services Act 37 of 2002. ___ ---(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] Installing Postgres 8.1 on Windows Server 2003 R2
Carl Conard wrote: Connections are through localhost. We've also connected via a client machine through a router to insure it is not something on the server. No, I meant what client library: odbc, jdbc .net libpq? By drop connections, I mean Task Manager is showing additional postgres.exe tasks after the completion of the test. Also, when we try to drop the DB to reset for another test, PGAdmin reports connections. Are you certain the application is disconnecting properly? Finally, of the 20 virtual users, any where from 2 to 12 or so will successfully complete the test (by adding information to the DB via our PHP app). The postmaster can be shut down manually with no issues after the test. However, upon rebooting the machine, I get IIS Helper Failed messages. I don't know if this is related or not. OK, so you're connecting from PHP running on IIS by the sound of it. Logs don't really show anything. At least nothing I can find. Are they showing connections and disconnects? If not, check you've turned this on in your postgresql.conf The only changes to the config file was to enable the logging at verbose and info levels. I can send the file if you'd like to see it. All you should need at the moment is connection logging and perhaps statement logging (to see what queries get executed). I've noticed a number of issues about beta releases dealing with sockets and such. I haven't found anything indicating they were fixed or if there are work arounds. It wouldn't be released if connections failed randomly. There have been issues with network performance on Win2K machines, but that seems to be sorted once the QoS add-on gets installed. I think what you need is something like: 1. A copy of the PostgreSQL logs showing each connection/disconnect. 2. A log from your application code showing where it connects/disconnects and the result codes it gets for each. 3. A count of how many connections are still present at the end of your test. This should fairly quickly show where the problem is. If it doesn't then we'll need to either: 1. turn on statement logging too to see if there is a pattern. 2. Reduce the application to just connect/disconnect and see if the probem persists. My guess as to the source of this problem would be: 1. Application error - some code-path where a disconnect doesn't actually happen. Because PG listens over an IP socket on Windows it'll sit there until the connection times out. 2. Some issue with IIS/PHP running threaded and the connection library not. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [SQL] hi all......................!!
AKHILESH GUPTA wrote: hello everybody i am new to this mailing list. this is my first mail to this group. i jussst want to confirm that whether is it possible to update a view or not?? There is no automatic updating of views at present. You can write your own RULES though to do updates - see the manuals for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [SQL] hi all......................!!
Hi, Akilesh, AKHILESH GUPTA wrote: i am new to this mailing list. this is my first mail to this group. i jussst want to confirm that whether is it possible to update a view or not?? i think you all help me in solving my queries in future...!! Do you think about issuing UPDATE commands on a view, or do you think about updating the view definition itsself? The former is possible if you add the appropriate 'ON UPDATE DO INSTEAD' Rules to the view, see http://www.postgresql.org/docs/8.1/static/rules.html and http://www.postgresql.org/docs/8.1/static/sql-createrule.html The latter is easily possible if the updated view definition has equal column definitions, just use CREATE OR UPDATE VIEW ... instead of CREATE VIEW ... to update the view. If your column definitions change, then you'll have to DROP the view before reCREATEing it, maybe it's best to encapsulate this inside a transaction or use a scheduled downtime. Btw, it seems that your '.'-key is broken and chatters. :-) HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Constraint that compares and limits field values
[EMAIL PROTECTED] wrote: I have a table that I am using to hold keys for M:M relationships. I have six fields that can hold the keys and I do this because I validate the key with a foreign key constraint. Fields evevid1, evevid2 hold keys from the event table, evreid1, evreid2 hold keys from the resource table, etc. The 0 works with the FK constraints because in each table being referenced I have a record with id = 0 that is empty. Each row should only have two foreign key values and the other key field values are 0. How do I put a constraint on the Insert / Update to make sure that only two fields out of the six have a value 0 in them. Are you sure you don't want NULL rather than a fake row? You can do the tests with a check constraint, although it'll look a bit clumsy. Here's a simplified example that ensures two zeroes per row. CREATE TABLE foo (a int, b int, c int); ALTER TABLE foo ADD CONSTRAINT two_zeroes CHECK ((a=0 AND b=0) OR (b=0 AND c=0) OR (a=0 AND c=0)); INSERT INTO foo VALUES (1,0,0); INSERT INTO foo VALUES (0,1,0); INSERT INTO foo VALUES (0,1,1); ERROR: new row for relation foo violates check constraint two_zeroes I think you probably want to use null for foreign-keys that aren't referencing anything though. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Shared Database across multiple servers using OCFS2
Kleynhans, Hendrik wrote: Hi there, Does anyone know if it is possible to share a single database across multiple machines each running a database server using OCFS2? Are you: 1. Looking to spread disk-accesses over several machines, or 2. Looking to run multiple PostgreSQL server against the same database? You might be ok with #1, but not #2. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Quoted NULLs with COPY FROM (and pgAdmin export data
George Pavlov wrote: This is actually turning into a bit of a pgAdmin issue: pgAdmin lets you export data in a format that seems to be unimportable back into the same table. If in the Export data to file form you check all columns under Quoting you will get quotes around your numeric NULLs that you will be unable to import back using COPY without resorting to preprocessing of some sort (unless someone tells me how COPY can use quoted NULLs). Does sound like something for the pgadmin team. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Please, help! About database cluster and adding to it additional disk space
Hi, List! Lets suppose the next situation... We create database cluster (via initdb) on some disk. Then we create and use a database that can be very large. In some moment the database occupy all disk space allicated to cluster. The question is: are there any abilities in PostgreSQL to use for created cluster additional disk space in new other disk? Similar ability exist in Informix... Thanks in advance for any answers! Sergey Karin
Re: [GENERAL] Please, help! About database cluster and adding to it additional disk space
On Wed, Jan 25, 2006 at 12:40:36PM +0300, Sergey Karin wrote: Hi, List! Lets suppose the next situation... We create database cluster (via initdb) on some disk. Then we create and use a database that can be very large. In some moment the database occupy all disk space allicated to cluster. The question is: are there any abilities in PostgreSQL to use for created cluster additional disk space in new other disk? Similar ability exist in Informix... You didn't say which version you are running, but recent versions have tablespaces which do what you want. Alternativly, if you're running LVM somewhere, you could make the disk postgres is no bigger... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] Shared Database across multiple servers using OCFS2
Hi Richard, : Are you: : 1. Looking to spread disk-accesses over several machines, or : 2. Looking to run multiple PostgreSQL server against the same : database? : : You might be ok with #1, but not #2. Thanks for the quick response. I am looking at #2, but I could not find anything from my searches. If I am looking to access large amounts of data, such as in a data center, would it be better to run a/several database servers and accessing them across the network? Kind regards, Henry ___ The information contained in this e-mail is confidential and may contain proprietary information. It is meant solely for the intended recipient. Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted in reliance on this, is prohibited and may be unlawful .No liability or responsibility is accepted if information or data is, for whatever reason corrupted or does not reach its intended recipient. No warranty is given that this e-mail is free of viruses. The views expressed in this e-mail are, unless otherwise stated, those of the author and not those of FirstRand Bank Limited or its management. FirstRand Bank Limited reserves the right to monitor, intercept and block e-mails addressed to its users or take any other action in accordance with its e-mail use policy. Licensed divisions of FirstRand Bank Limited are authorised financial service providers in terms of the Financial Advisory and Intermediary Services Act 37 of 2002. ___ ---(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] Please, help! About database cluster and adding to
Sergey Karin wrote: Hi, List! Lets suppose the next situation... We create database cluster (via initdb) on some disk. Then we create and use a database that can be very large. In some moment the database occupy all disk space allicated to cluster. The question is: are there any abilities in PostgreSQL to use for created cluster additional disk space in new other disk? Similar ability exist in Informix... Well, there are various filesystem add-ons that allow you to have virtual partitions spread over one or more physical disks. I'm guessing you're not using any of those. You can move the WAL to a different disk and use table-spaces to relocate tables and indexes. See the manuals for tablespace setup and check the mailing-list archives for how to do it the hard way with symbolic links if you're running an old version of PG. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Shared Database across multiple servers using OCFS2
Kleynhans, Hendrik wrote: Hi Richard, : Are you: : 1. Looking to spread disk-accesses over several machines, or : 2. Looking to run multiple PostgreSQL server against the same : database? : : You might be ok with #1, but not #2. Thanks for the quick response. I am looking at #2, but I could not find anything from my searches. PostgreSQL backend processes communicate using shared memory - if you can't manage that between different machines then it won't work. If I am looking to access large amounts of data, such as in a data center, would it be better to run a/several database servers and accessing them across the network? Difficult to say without more information. You'll need to know how much data, what queries, how many at one time etc. You might want to check the mailing-list archives (particularly the performance list) and see if any other users have a similar setup to that which you are planning. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Shared Database across multiple servers using OCFS2
On Jan 25, 2006, at 18:43 , Kleynhans, Hendrik wrote: If I am looking to access large amounts of data, such as in a data center, would it be better to run a/several database servers and accessing them across the network? If you want *access* (i.e., select only, not modifying data), you might want to look at Slony for database replication. You can have your applications access the slaves. All modification would be done on the master. http://gborg.postgresql.org/project/slony1/projdisplay.php Michael Glaesemann grzm myrealbox com ---(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] Please, help! About database cluster and adding to
Hi, On Wed, 2006-01-25 at 12:40 +0300, Sergey Karin wrote: We create database cluster (via initdb) on some disk. Then we create and use a database that can be very large. In some moment the database occupy all disk space allicated to cluster. The question is: are there any abilities in PostgreSQL to use for created cluster additional disk space in new other disk? Similar ability exist in Informix... You should either use LVM or tablespaces for this. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] NOT HAVING clause?
Andrew - Supernews wrote: On 2006-01-24, Will Glynn [EMAIL PROTECTED] wrote: You might try: SELECT some_column FROM some_table GROUP BY some_column HAVING SUM(CASE WHEN sort_order=1 THEN 1 ELSE 0 END) = 0; SELECT some_column FROM some_table GROUP BY some_column HAVING every(sort_order 1); every() is in 8.1 at least (can't recall when it was introduced); it's the same as bool_and(), i.e. an aggregate that returns true only if all inputs are true. Why isn't there a corresponding any(), I wonder? (bool_or does exist) Unfortunately we still use 7.4, but I realized this morning that this should work too (not tried yet): SELECT some_column FROM some_table GROUP BY some_column HAVING MIN(sort_order) 1; As our sort_orders start from 1. -- 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 //Showing your Vision to the World// ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Temporary table visibility
Hi all, I've had a look at through the list archives but haven't found an answer to this one. Any suggestions appreciated (aside from ones suggesting that I should not need to do this ;-)... - A normal table foo is created in a database. - Clients connect to the database, some create a temp table foo some don't. (only one postgresql user is being used to connect to the database if that matters) How does a client determine if table foo is temporary or not? Or put another way... How can I determine what temporary tables exist in my session, bearing in mind that other sessions contain temp tables using the same names? Many thanks, James -- This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. Any offers or quotation of service are subject to formal specification. Errors and omissions excepted. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Lumison, nplusone or lightershade ltd. Finally, the recipient should check this email and any attachments for the presence of viruses. Lumison, nplusone and lightershade ltd accepts no liability for any damage caused by any virus transmitted by this email. -- -- Virus scanned by Lumison. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Does this look ethical to you?
I can't agree more. -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Wednesday, 25 January 2006 15:16 To: Tony Caduto Cc: Magnus Hagander; Dave Page; pgsql-general@postgresql.org Subject: Re: Does this look ethical to you? The people who develop, package, and host pginstaller files are doing so to promote open source software for users, not to help you sell commercial software. For that, you are on your own. If you want to find volunteers to help you promote and sell your software, good luck. :-) --- Tony Caduto wrote: Magnus Hagander wrote: We (pginstaller hat goes on) don't know of any competing products. We will be happy to consider bundling any competing product, including PG Lightning Admin. One of the most important things in order to be distributed as part of an open source product is that the parts are open source. If PGLA (or a lite version if necessary) is available under an OSS license, we'll definitly consider bundling it. (We have considered bundling phpPgAdmin, but haven't found a good way to do it without dragging in a huge load of dependencies) I don't wan't to be bundled, I just want it to be known that there are alternatives available. Postgresql is free, so what is the big deal about letting users know about alternative admin tools comercial or open source? It's not like it would put pgAdmin or Postgresql out of business(it could only help). It's a shame you don't see how the bundling of pgAdmin(in the current way) is hurting the 3rd party community. All that would be needed is a installer section at the end saying something like: In addition to pgAdmin III there are other opensource and commercial admin products available, you can get more information here link back to postgresql home page. I don't see that as being detrimental to anyone and would certainly be in fair play. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] table is not a table
Hello! Could you please help me with an issue I have on a PG installation. The problem is it is impossible to drop any table. Looks like this. == [EMAIL PROTECTED] bin]$ ./psql postgres ilejn Welcome to psql 8.1.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# create table ddd(f1 int4); CREATE TABLE postgres=# drop table ddd; ERROR: ddd is not a table postgres=# select * from pg_authid where rolname='ilejn'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil | rolconfig -+--++---+-+--+-+--+-+---+--- ilejn | t| t | t | t | t| t | -1 | | | (1 row) postgres=# select * from pg_tables where tablename='ddd'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers +---++++--+- public | ddd | ilejn || f | f| f (1 row) postgres=# alter table ddd add column f2 text; ALTER TABLE == There is no magic about 'ddd' - same thing for every table. I can ALTER this 'ddd', I can do any DML, but cannot drop! Any ideas what's wrong with permissions/roles or something? Thanks. -- Best regards Ilja Golshtein ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Does this look ethical to you?
Andrew Maclean napisał(a): I can't agree more. -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Wednesday, 25 January 2006 15:16 To: Tony Caduto Cc: Magnus Hagander; Dave Page; pgsql-general@postgresql.org Subject: Re: Does this look ethical to you? The people who develop, package, and host pginstaller files are doing so to promote open source software for users, not to help you sell commercial software. For that, you are on your own. If you want to find volunteers to help you promote and sell your software, good luck. :-) Maybe Tony plans to donate part of the incomging back to the PostgreSQL project :-) Sergiusz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Tsearch 2
Hi, How the tsearch2 work? I can use it in my project that is language Português (Brasil)? Thanks. Marcos. ---(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] FATAL: terminating connection due to administrator command
Title: Re: [GENERAL] FATAL: terminating connection due to administrator command is it also possible that someone was doing an operation on the database for instance inserting manyr rows and suddenly a command to stop the postmaster arrived? thanks, regards Surabhi From: Tom Lane [mailto:[EMAIL PROTECTED]Sent: Tue 1/24/2006 8:22 PMTo: Richard HuxtonCc: surabhi.ahuja; pgsql-general@postgresql.orgSubject: Re: [GENERAL] FATAL: terminating connection due to administrator command ***Your mail has been scanned by iiitb VirusWall.***-***Richard Huxton dev@archonet.com writes: surabhi.ahuja wrote: so does this mean that someone is trying to stop postmaster by sending it a kill signal? Someone or something. It can be Linux's out-of-memory facility picking processes to kill. Google "oom killer" for discussion.No, because the OOM killer invariably uses "kill -9". "Fast shutdown"means that something sent the postmaster a SIGINT.If you launch the postmaster manually and are not careful to make itdissociate from your terminal, then typing ^C at some unrelated programlater would be enough to make this happen ... 1. many times i have seen two instances of postmaster running. how does that happen and how to prevent it from happening? Shouldn't (unless you have two installations of course).Perhaps he's not understanding the difference between the postmaster andits child processes? I don't believe he's actually got two postmastersrunning (unless maybe in separate directories with separate ports, whichis hardly likely to be a setup one would create by accident). There are*very* extensive safety interlocks in place to prevent that. regards, tom lane
[GENERAL] 2 instances of postmaster with different data directories
Hi, I am going to integrate my dtabase into a system. That system also has another database and uses postgres. However they have their own data directory and start postmaster by specifying that. I have seen that it is possible to run multiple postmasters on multiple ports by specifying diffrent data directories. But we are still to decide if we should go withthe above approachor is it better to have just one data directory and one instance of postmaster on the default port. any amount of info will be valuable. thanks, regards Surabhi Ahuja
Re: [GENERAL] Temporary table visibility
How can I determine what temporary tables exist in my session, bearing in mind that other sessions contain temp tables using the same names? just the ones you have created in your session, temporary tables in other sessions are invisible to you... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] user defined function
I use 7.3 and use RECORD as the input data type of the function by create function foo(record) returns int4 as '$libdir/bar' language C. But I got this error msg: ERROR: parser: parse error at or near record at character. What is the problem? I look up the 7.3 manual. it seems record is a supported pseudo data type. On 1/24/06, Scott Marlowe [EMAIL PROTECTED] wrote: On Tue, 2006-01-24 at 14:38, Tom Lane wrote: Yl Zhou [EMAIL PROTECTED] writes: But I have to use 7.3 due to some limitations. Can I do it in 7.3? Probably, but I forget how (and I can guarantee that it will break when you do move to 8.0 or later, because we changed the internal representation of rowtype arguments). You'd be *much* better off to spend your time fixing whatever it is that's keeping you on 7.3. For some reason I'm remember 7.4 as being the first version that let you do this. Not for certain. I didn't run 7.3 in production though, so I might have missed it if it could do this. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Temporary table visibility
On 25 Jan 2006, at 14:17, Jaime Casanova wrote: How can I determine what temporary tables exist in my session, bearing in mind that other sessions contain temp tables using the same names? just the ones you have created in your session, temporary tables in other sessions are invisible to you... Thanks Jaime but that's not really what I meant. I know that if a session creates a temporary table it is only visible to that session. I'm not doing a good job of explaining this but basically given the following results... test= select relname, relnamespace, reltype from pg_class where relname = 'session_data'; relname| relnamespace | reltype --+--+-- session_data | 2200 | 16114367 session_data | 16120903 | 16314010 session_data | 16120709 | 16314030 session_data | 16122659 | 16314133 session_data | 16123201 | 16314285 session_data | 16124398 | 16315049 session_data |16767 | 16315527 session_data | 16120382 | 16315818 session_data | 16125558 | 16315816 session_data | 16114413 | 16316810 session_data | 16127654 | 16317471 session_data | 16114683 | 16317551 session_data | 16118447 | 16317563 session_data | 15035529 | 16317579 (14 rows) How can I determine if one of the above relations is a temporary table in the current session (one of them, the first in ns 2200, is a normal permanent table)? Thanks, James -- This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. Any offers or quotation of service are subject to formal specification. Errors and omissions excepted. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Lumison, nplusone or lightershade ltd. Finally, the recipient should check this email and any attachments for the presence of viruses. Lumison, nplusone and lightershade ltd accepts no liability for any damage caused by any virus transmitted by this email. -- -- Virus scanned by Lumison. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Shared Database across multiple servers using OCFS2
Kleynhans, Hendrik wrote: snip ___ “The information contained in this e-mail is confidential and may contain proprietary information. It is meant solely for the intended recipient. Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted in reliance on this, is prohibited and may be unlawful .No liability or responsibility is accepted if information or data is, for whatever reason corrupted or does not reach its intended recipient. No warranty is given that this e-mail is free of viruses. The views expressed in this e-mail are, unless otherwise stated, those of the author and not those of FirstRand Bank Limited or its management. FirstRand Bank Limited reserves the right to monitor, intercept and block e-mails addressed to its users or take any other action in accordance with its e-mail use policy. Licensed divisions of FirstRand Bank Limited are authorised financial service providers in terms of the Financial Advisory and Intermediary Services Act 37 of 2002.” ___ That has got to be one of the longest disclaimers I've ever seen in an email... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Missing database entry in pg_database
Hi, I have a problem with a database i'm maintaining. I first noticed the problem because i could not make a backup of the database i got the following error: pg_dump: missing pg_database entry for database xxx I verified this by selecting the pg_database. It was indeed gone. I did some more diggin and noticed that on doing a describe (\d table) of a table i could not see any or some of the columns in the table, and a few tables i also could just see the correct layout. It looks random. The database is a very active database. It is running on Postgresql 7.3. The database is getting a VACUUM FULL ANALYZE every night. No updates where made on the machine and the error did not accour after a human action as far as i can see. Can someone tell me what happened here? Can i fix my system table? Can i still trust the other databases on the system? -- You can't reach second base, and keep your foot on first. Groeten, Robert ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Temporary table visibility
On 1/25/06, James Croft [EMAIL PROTECTED] wrote:
On 25 Jan 2006, at 14:17, Jaime Casanova wrote:
How can I determine what temporary tables exist in my session,
bearing in mind that other sessions contain temp tables using the
same names?
just the ones you have created in your session, temporary tables in
other sessions are invisible to you...
Thanks Jaime but that's not really what I meant.
I know that if a session creates a temporary table it is only visible
to that session. I'm not doing a good job of explaining this but
basically given the following results...
test= select relname, relnamespace, reltype from pg_class where
relname = 'session_data';
relname| relnamespace | reltype
--+--+--
session_data | 2200 | 16114367
session_data | 16120903 | 16314010
session_data | 16120709 | 16314030
session_data | 16122659 | 16314133
session_data | 16123201 | 16314285
session_data | 16124398 | 16315049
session_data |16767 | 16315527
session_data | 16120382 | 16315818
session_data | 16125558 | 16315816
session_data | 16114413 | 16316810
session_data | 16127654 | 16317471
session_data | 16114683 | 16317551
session_data | 16118447 | 16317563
session_data | 15035529 | 16317579
(14 rows)
How can I determine if one of the above relations is a temporary
table in the current session (one of them, the first in ns 2200, is a
normal permanent table)?
Thanks,
James
SELECT n.nspname as Schema, c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'índex' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
Type,
r.rolname as Owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname LIKE 'pg_temp%'
AND pg_catalog.pg_table_is_visible(c.oid);
Maybe this is what you want?
FWIW, this was make just with psql -E (to view what query \d executes
and changing the AND n.nspname NOT IN line for something more
apropiate...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(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
[GENERAL] Alternative to knoda, kexi and rekall?
Hello, I am using PostgreSQL since more then 6 years now and for 1 1/2 years rekall. Now there is a problem with the crapy QT and I have no Frontend anymore which works WITHOUT (!!!) KDE or GNOME which I hate! Currently I am using pgAccess to check my PostgreSQL but it is very limited. Does anyone know a Frontend for PostgreSQL which I can use to design and admin a very huge Database (over 160 GByte and grown; the biggest table is over 120 GByte) I need it urgentiel under plain/x without GNOME and KDE. If OSS is not availlable, a commercial product? I am not a PostgreSQL guru, but since I have lost last year my two Iranien programmers, I am working alone and need support in form of good Software. Please note, that I am using Debian GNU/Linux 3.0 and 3.1 and NO, I WILL NOT SWITCH TO WINDOWS, EVEN THERE ARE VERY GOOD GUI'S FOR POSTGRESQL. I wish, such GUI's exist under Linux! Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgresql Segfault in 8.1
Benjamin Smith [EMAIL PROTECTED] writes: What's the best way to do this? Take PG down (normally started as a service) and run directly in a single-user mode? No, just start a psql session in one window, then in another window determine the PID of the backend process it's connected to, and attach gdb to that process. Something like ps auxww | grep postgres: ... eyeball determination of correct PID ... gdb /path/to/postgres-executable PID gdb continue Now, in the psql window, do what's needed to provoke the crash. gdb should trap at the instant of the segfault and give you another gdb prompt. Type bt to get the backtrace, then q to disconnect. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Alternative to knoda, kexi and rekall?
Michelle Konzack wrote: Does anyone know a Frontend for PostgreSQL which I can use to design and admin a very huge Database OpenOffice 2? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Alternative to knoda, kexi and rekall?
On Wed, Jan 25, 2006 at 04:43:18PM +0100, Michelle Konzack wrote: Hello, I am using PostgreSQL since more then 6 years now and for 1 1/2 years rekall. Now there is a problem with the crapy QT and I have no Frontend anymore which works WITHOUT (!!!) KDE or GNOME which I hate! You mean you want a frontend that does not use GTK+ or QT? Just plain Xt or Athena? Good luck, as straight X toolkit is quite complicated compared to those two. I just use psql, but you appear to feel you need a graphical client. apt-cache search postgresql seems to suggest: dbengine - A plug 'n play Web interface for mySQL and PostgreSQL dbishell - Interactive SQL shell with readline support pgaccess - Tk/Tcl interface to PostgreSQL sql-editor - editor of SQL databases, with 'join' capability Anyone know any other non-text based clients? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] Temporary table visibility
Jaime Casanova [EMAIL PROTECTED] writes:
On 1/25/06, James Croft [EMAIL PROTECTED] wrote:
How can I determine if one of the above relations is a temporary
table in the current session (one of them, the first in ns 2200, is a
normal permanent table)?
SELECT n.nspname as Schema, c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'índex' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
Type,
r.rolname as Owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname LIKE 'pg_temp%'
AND pg_catalog.pg_table_is_visible(c.oid);
Close, but you really ought to escape the _ to avoid it being a LIKE
wildcard. I'd tend to use a regex instead since _ isn't a wildcard
in regex patterns. So the essential part of this is something like
select relname
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where nspname ~ '^pg_temp_'
and pg_catalog.pg_table_is_visible(c.oid);
The test on the namespace name tells you it's temp (yes, this is a
legit way to do it, it's the same way the backend decides it's a
temp namespace) and the test on visibility is an easy way to see if
it's your temp namespace or someone else's.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] FW: deleted records
On Wed, Jan 25, 2006 at 11:42:04AM +0100, H.J. Sanders wrote:
When I do this I get the message
relation pgstattuple does not exist.
pgstattuple is a function, not a relation, so I suspect the query
isn't referring to it correctly. What's the exact query you ran?
It should look like this:
SELECT * FROM pgstattuple('tablename');
Did you install the contrib/pgstattuple module?
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgxml
On Tue, 2006-01-24 at 21:34, Tom Lane wrote: [EMAIL PROTECTED] [EMAIL PROTECTED] writes: I’ am looking for pgxml module for postgres 7.2.1, someone can help me to find it? *Please* tell me you are not still using PG 7.2.1. There are seven subsequent releases in the 7.2 series, each containing fixes for very serious bugs --- see http://developer.postgresql.org/docs/postgres/release-7-2-8.html and following pages. Furthermore, 7.2.* has officially been abandoned as unsupported: there are numerous known bugs in 7.2.8 that aren't going to be fixed, ever. Rather than looking for pgxml for 7.2, you desperately need to be spending your time on updating to some non-stone-age version of Postgres. I wonder if they're running one of those ancient windows ports of 7.2.1. I seem to remember one being done on that code branch wayyy back in the day. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Temporary table visibility
On 1/25/06, Tom Lane [EMAIL PROTECTED] wrote:
Jaime Casanova [EMAIL PROTECTED] writes:
On 1/25/06, James Croft [EMAIL PROTECTED] wrote:
How can I determine if one of the above relations is a temporary
table in the current session (one of them, the first in ns 2200, is a
normal permanent table)?
SELECT n.nspname as Schema, c.relname as Name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'índex' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
Type,
r.rolname as Owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname LIKE 'pg_temp%'
AND pg_catalog.pg_table_is_visible(c.oid);
Close, but you really ought to escape the _ to avoid it being a LIKE
wildcard. I'd tend to use a regex instead since _ isn't a wildcard
in regex patterns. So the essential part of this is something like
jeje... need more coffee... and i really have to put my hands on that
regex book on the corner...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Alternative to knoda, kexi and rekall?
On Wednesday 25 January 2006 17:01, Martijn van Oosterhout wrote: Anyone know any other non-text based clients? I'm using PHP with plain old HTML forms. That way, I can slap together a custom interface in no time. And it even works in links2 :) -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] table is not a table
Ilja Golshtein [EMAIL PROTECTED] writes: postgres=# create table ddd(f1 int4); CREATE TABLE postgres=# drop table ddd; ERROR: ddd is not a table That's just plain bizarre. Would you try it with \set VERBOSITY verbose so we can see exactly where the error is coming from? Has this installation been working for you before? I'm wondering about a corrupt backend executable file, or some such ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] user defined function
andrew [EMAIL PROTECTED] writes: I use 7.3 and use RECORD as the input data type of the function by create function foo(record) returns int4 as '$libdir/bar' language C. But I got this error msg: ERROR: parser: parse error at or near record at character. What is the problem? Sure you typed it correctly? I get regression=# create function foo(record) returns int4 as '$libdir/bar' language C; ERROR: stat failed on file '$libdir/bar': No such file or directory regression=# so it's getting past the parse-error stage here. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Constraint that compares and limits field values
Richard, I have taken your suggestion and changed the values to NULL for the empty foreign keys. Thank you for the constraint. I modified it to check for NULL and it works great. ALTER TABLE event ADD CONSTRAINT two_nulls_1 CHECK (evenid1 IS NULL) AND (evevid1 IS NULL)) OR ((evevid1 IS NULL) AND (evreid1 IS NULL))) OR ((evenid1 IS NULL) AND (evreid1 IS NULL; ALTER TABLE event ADD CONSTRAINT two_nulls_2 CHECK (evenid2 IS NULL) AND (evevid2 IS NULL)) OR ((evevid2 IS NULL) AND (evreid2 IS NULL))) OR ((evenid2 IS NULL) AND (evreid2 IS NULL; *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 This e-mail message and any attachment(s) are for the sole use of the intended recipient(s) and may contain proprietary and/or confidential information which may be privileged or otherwise protected from disclosure. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient(s), please contact the sender by reply email and destroy the original message and any copies of the message as well as any attachment(s) to the original message. Richard Huxton dev@archonet.com 01/25/2006 01:33 AM To [EMAIL PROTECTED] cc pgsql-general@postgresql.org Subject Re: [GENERAL] Constraint that compares and limits field values [EMAIL PROTECTED] wrote: I have a table that I am using to hold keys for M:M relationships. I have six fields that can hold the keys and I do this because I validate the key with a foreign key constraint. Fields evevid1, evevid2 hold keys from the event table, evreid1, evreid2 hold keys from the resource table, etc. The 0 works with the FK constraints because in each table being referenced I have a record with id = 0 that is empty. Each row should only have two foreign key values and the other key field values are 0. How do I put a constraint on the Insert / Update to make sure that only two fields out of the six have a value 0 in them. Are you sure you don't want NULL rather than a fake row? You can do the tests with a check constraint, although it'll look a bit clumsy. Here's a simplified example that ensures two zeroes per row. CREATE TABLE foo (a int, b int, c int); ALTER TABLE foo ADD CONSTRAINT two_zeroes CHECK ((a=0 AND b=0) OR (b=0 AND c=0) OR (a=0 AND c=0)); INSERT INTO foo VALUES (1,0,0); INSERT INTO foo VALUES (0,1,0); INSERT INTO foo VALUES (0,1,1); ERROR: new row for relation foo violates check constraint two_zeroes I think you probably want to use null for foreign-keys that aren't referencing anything though. -- Richard Huxton Archonet Ltd
Re: [GENERAL] Missing database entry in pg_database
Robert Korteweg [EMAIL PROTECTED] writes: I have a problem with a database i'm maintaining. I first noticed the problem because i could not make a backup of the database i got the following error: pg_dump: missing pg_database entry for database xxx I verified this by selecting the pg_database. It was indeed gone. I did some more diggin and noticed that on doing a describe (\d table) of a table i could not see any or some of the columns in the table, and a few tables i also could just see the correct layout. It looks random. This sounds suspiciously like a transaction ID wraparound problem. The database is a very active database. It is running on Postgresql 7.3. The database is getting a VACUUM FULL ANALYZE every night. The *entire* database ... or are you just vacuuming the user tables and not the system catalogs? Daily vacuuming of the catalogs should have prevented any such problem (unless you are managing to exceed 1 billion transactions per day...) 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] user defined function
The following is just copied from the screen. backend create function foo(record) returns int4 as '$libdir/bar' language C QUERY: create function foo(record) returns int4 as '$libdir/bar' language C ERROR: parser: parse error at or near record at character 21 in Warn_restart code What is the problem here? Did you test it on 7.3? On 1/25/06, Tom Lane [EMAIL PROTECTED] wrote: andrew [EMAIL PROTECTED] writes: I use 7.3 and use RECORD as the input data type of the function by create function foo(record) returns int4 as '$libdir/bar' language C. But I got this error msg: ERROR: parser: parse error at or near record at character. What is the problem? Sure you typed it correctly? I get regression=# create function foo(record) returns int4 as '$libdir/bar' language C; ERROR: stat failed on file '$libdir/bar': No such file or directory regression=# so it's getting past the parse-error stage here. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] user defined function
andrew [EMAIL PROTECTED] writes: ERROR: parser: parse error at or near record at character 21 in Warn_restart code What is the problem here? Did you test it on 7.3? Yeah, 7.3.13 to be exact. (There have been a couple of changes in the parser in the 7.3 branch, according to the CVS logs, but none look to be related to this.) Where did that in Warn_restart code bit come from? There's no such string anywhere in the 7.3 sources. Perhaps you are playing with a copy that someone has modified/broken? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Constraint that compares and limits field values
On Wed, Jan 25, 2006 at 08:39:00AM -0800, [EMAIL PROTECTED] wrote: I have taken your suggestion and changed the values to NULL for the empty foreign keys. Thank you for the constraint. I modified it to check for NULL and it works great. ALTER TABLE event ADD CONSTRAINT two_nulls_1 CHECK (evenid1 IS NULL) AND (evevid1 IS NULL)) OR ((evevid1 IS NULL) AND (evreid1 IS NULL))) OR ((evenid1 IS NULL) AND (evreid1 IS NULL; Is there a requirement that exactly one column be NOT NULL? If so then you'll need to add a check for that because this constraint would allow all three to be NULL. -- Michael Fuhr ---(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
[GENERAL] My very first PL/pgSQL procedure...
Hi,
I've got a problem with my very first PL/pgSQL procedure !
I created the following procedure, that should reconfigure a sequence :
-
CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
current_seq integer;
BEGIN
LOCK TABLE sequence_name IN ACCESS EXCLUSIVE MODE;
current_seq := last_value FROM sequence_name;
IF current_seq minval THEN
ALTER SEQUENCE sequence_name RESTART WITH minval;
END IF;
END;
$$ LANGUAGE plpgsql;
-
I call it from the psql interface by :
SELECT seq_min('seq_mytable', 1029);
But PostgreSQL returns the following error (translated from french) :
ERROR: syntax error on or near «$1» at character 13
QUERY : LOCK TABLE $1 IN ACCESS EXCLUSIVE MODE
CONTEXT : PL/pgSQL function seq_min line 4 at SQL statement
LINE 1 : LOCK TABLE $1 IN ACCESS EXCLUSIVE MODE
So it seems that PostgreSQL have troubles handling my variable
sequence_name...
Any idea ?
Thank you in advance,
Philippe Ferreira, France.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] user defined function
Sorry, I modified the parser code and forgot abt it. Now there is no problem in creating the function. But there is another problem. I create a function to accept record type parameter. But when I call it on a specific composite type, error is reported. The followings are what I have done: backend create function complete(record) returns int4 as '$libdir/qualityudf' language C QUERY: create function complete(record) returns int4 as '$libdir/qualityudf' language C backend select *, complete(Person) from Person QUERY: select *, complete(Person) from Person ERROR: Function complete(person) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts On 1/25/06, Tom Lane [EMAIL PROTECTED] wrote: andrew [EMAIL PROTECTED] writes: ERROR: parser: parse error at or near record at character 21 in Warn_restart code What is the problem here? Did you test it on 7.3? Yeah, 7.3.13 to be exact. (There have been a couple of changes in the parser in the 7.3 branch, according to the CVS logs, but none look to be related to this.) Where did that in Warn_restart code bit come from? There's no such string anywhere in the 7.3 sources. Perhaps you are playing with a copy that someone has modified/broken? regards, tom lane -- andrew ---(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] table is not a table
On Wed, Jan 25, 2006 at 11:26:39AM -0500, Tom Lane wrote: Ilja Golshtein [EMAIL PROTECTED] writes: postgres=# create table ddd(f1 int4); CREATE TABLE postgres=# drop table ddd; ERROR: ddd is not a table That's just plain bizarre. Would you try it with \set VERBOSITY verbose so we can see exactly where the error is coming from? Has this installation been working for you before? I'm wondering about a corrupt backend executable file, or some such ... Could a corrupt catalog be responsible? Might a query like the following reveal anything? SELECT c.ctid, c.xmin, c.xmax, c.oid, c.relname, c.relkind, n.ctid, n.xmin, n.xmax, n.oid, n.nspname FROM pg_class AS c LEFT JOIN pg_namespace AS n ON n.oid = c.relnamespace WHERE c.relname ~* '^ddd'; -- Michael Fuhr ---(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] Postgresql Segfault in 8.1
* Tom Lane ([EMAIL PROTECTED]) wrote: Benjamin Smith [EMAIL PROTECTED] writes: What's the best way to do this? Take PG down (normally started as a service) and run directly in a single-user mode? No, just start a psql session in one window, then in another window determine the PID of the backend process it's connected to, and attach gdb to that process. Something like ps auxww | grep postgres: ... eyeball determination of correct PID ... You can also do 'select pg_backend_pid();' from psql... I know that's there on 8.1, though I recall this was an 8.0 discussion and I *think* it's there too but not 100% sure. Enjoy, Stephen signature.asc Description: Digital signature
[GENERAL] filtering after join
I want to use a UDF to filter tuples t that are generated after a join. More specifially, I have a UDF foo(record), which computes a value for a given tuple. I can do the filtering before the join. e.g.: select * from A, B where foo(A)2 and A.a=B.b; But I want to apply foo() to the tuples generated by the join operation. How can I do that? Thanks! -- andrew ---(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] user defined function
sorry, mistakenly leave out another try: backend select *, complete(CAST (Person AS record)) from Person QUERY: select *, complete(CAST (Person AS record)) from Person ERROR: Relation reference person cannot be used in an expression On 1/25/06, andrew [EMAIL PROTECTED] wrote: Sorry, I modified the parser code and forgot abt it. Now there is no problem in creating the function. But there is another problem. I create a function to accept record type parameter. But when I call it on a specific composite type, error is reported. The followings are what I have done: backend create function complete(record) returns int4 as '$libdir/qualityudf' language C QUERY: create function complete(record) returns int4 as '$libdir/qualityudf' language C backend select *, complete(Person) from Person QUERY: select *, complete(Person) from Person ERROR: Function complete(person) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts On 1/25/06, Tom Lane [EMAIL PROTECTED] wrote: andrew [EMAIL PROTECTED] writes: ERROR: parser: parse error at or near record at character 21 in Warn_restart code What is the problem here? Did you test it on 7.3? Yeah, 7.3.13 to be exact. (There have been a couple of changes in the parser in the 7.3 branch, according to the CVS logs, but none look to be related to this.) Where did that in Warn_restart code bit come from? There's no such string anywhere in the 7.3 sources. Perhaps you are playing with a copy that someone has modified/broken? regards, tom lane -- andrew -- andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] My very first PL/pgSQL procedure...
you need to use EXECUTE to do the dynamic lock table.
sql = 'LOCK TABLE ' || sequence_name || 'IN ACCESS EXCLUSIVE MODE';
EXECUTE sql;
-- Original Message ---
From: Philippe Ferreira [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Wed, 25 Jan 2006 18:37:21 +0100
Subject: [GENERAL] My very first PL/pgSQL procedure...
Hi,
I've got a problem with my very first PL/pgSQL procedure !
I created the following procedure, that should reconfigure a sequence :
-
CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
current_seq integer;
BEGIN
LOCK TABLE sequence_name IN ACCESS EXCLUSIVE MODE;
current_seq := last_value FROM sequence_name;
IF current_seq minval THEN
ALTER SEQUENCE sequence_name RESTART WITH minval;
END IF;
END;
$$ LANGUAGE plpgsql;
-
I call it from the psql interface by :
SELECT seq_min('seq_mytable', 1029);
But PostgreSQL returns the following error (translated from french) :
ERROR: syntax error on or near «$1» at character 13
QUERY : LOCK TABLE $1 IN ACCESS EXCLUSIVE MODE
CONTEXT : PL/pgSQL function seq_min line 4 at SQL statement
LINE 1 : LOCK TABLE $1 IN ACCESS EXCLUSIVE MODE
So it seems that PostgreSQL have troubles handling my variable
sequence_name...
Any idea ?
Thank you in advance,
Philippe Ferreira, France.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--- End of Original Message ---
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] xml support on win32 postgres
Ok, I decided to upgrade my postgress version to 8.1, I am using a win32 distribution, where can I find the xml2 extension? I got the pgxml distribution from http://www.u-moe.org/pgxml/ and I followed all tips from George • From: George Weaver gweaver ( at ) shaw ( dot ) ca • To: pgsql-novice ( at ) postgresql ( dot ) org • Subject: Re: PGXML support for postgreSQL on Windows • Date: Tue, 29 Nov 2005 07:30:43 -0600 But I am not able to install the xml2 module, I get always the same error: psql:../share/contrib/pgxml.sql:4: ERROR: could not load library C: /Programmi/ PostgreSQL/8.1/lib/pgxml.dll: Impossibile trovare il modulo specificato. psql:../share/contrib/pgxml.sql:7: ERROR: could not load library C: /Programmi/ PostgreSQL/8.1/lib/pgxml.dll: Impossibile trovare il modulo specificato. psql:../share/contrib/pgxml.sql:10: ERROR: could not load library C: /Programmi /PostgreSQL/8.1/lib/pgxml.dll: Impossibile trovare il modulo specificato. psql:../share/contrib/pgxml.sql:13: ERROR: could not load library C: /Programmi /PostgreSQL/8.1/lib/pgxml.dll: Impossibile trovare il modulo specificato. psql:../share/contrib/pgxml.sql:16: ERROR: could not load library C: /Programmi /PostgreSQL/8.1/lib/pgxml.dll: Impossibile trovare il modulo specificato.psql:../share/contrib/pgxml.sql:4: ERROR: could not load library C:/Programmi/ PostgreSQL/8.1/lib/pgxml.dll: Impossibile trovare il modulo specificato. psql:../share/contrib/pgxml.sql:7: ERROR: could not load library C: /Programmi/ PostgreSQL/8.1/lib/pgxml.dll: Impossibile trovare il modulo specificato. psql:../share/contrib/pgxml.sql:10: ERROR: could not load library C: /Programmi /PostgreSQL/8.1/lib/pgxml.dll: Impossibile trovare il modulo specificato. psql:../share/contrib/pgxml.sql:13: ERROR: could not load library C: /Programmi /PostgreSQL/8.1/lib/pgxml.dll: Impossibile trovare il modulo specificato. psql:../share/contrib/pgxml.sql:16: ERROR: could not load library C: /Programmi /PostgreSQL/8.1/lib/pgxml.dll: Impossibile trovare il modulo specificato. etc could you help to find an other version of pgxml module done for pg8. 1? Thanks a lot Bye Gianni ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Constraint that compares and limits field values
On Wed, Jan 25, 2006 at 09:55:58AM -0800, [EMAIL PROTECTED] wrote: ALTER TABLE event ADD CONSTRAINT two_nulls_1 CHECK ((evenid1 IS NULL) AND (evevid1 IS NULL) and (evreid1 IS NOT NULL)) OR ((evenid1 IS NULL) AND (evevid1 IS NOT NULL) and (evreid1 IS NULL)) OR ((evenid1 IS NOT NULL) AND (evevid1 IS NULL) and (evreid1 IS NULL)) Parhaps something like: CHECK((CASE WHEN evenid1 IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN evenid2 IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN evenid3 IS NOT NULL THEN 1 ELSE 0 END)) = 1; If you can find a function to turn a bool into an int it becomes even easier. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] Constraint that compares and limits field values
On Wed, Jan 25, 2006 at 09:55:58AM -0800, [EMAIL PROTECTED] wrote: You are correct, in each group of three columns, one needs to have an integer and the other two need to be NULL. So I need to modify the constraint to be ALTER TABLE event ADD CONSTRAINT two_nulls_1 CHECK ((evenid1 IS NULL) AND (evevid1 IS NULL) and (evreid1 IS NOT NULL)) OR ((evenid1 IS NULL) AND (evevid1 IS NOT NULL) and (evreid1 IS NULL)) OR ((evenid1 IS NOT NULL) AND (evevid1 IS NULL) and (evreid1 IS NULL)) Correct? That looks right, aside from a syntax error from not having parentheses around the entire expression. CREATE TABLE event ( id serial PRIMARY KEY, evenid1 integer, evevid1 integer, evreid1 integer ); ALTER TABLE event ADD CONSTRAINT two_nulls_1 CHECK ( ((evenid1 IS NULL) AND (evevid1 IS NULL) and (evreid1 IS NOT NULL)) OR ((evenid1 IS NULL) AND (evevid1 IS NOT NULL) and (evreid1 IS NULL)) OR ((evenid1 IS NOT NULL) AND (evevid1 IS NULL) and (evreid1 IS NULL))); INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, NULL, NULL); INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, NULL, 1); INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, 1, NULL); INSERT INTO event (evenid1, evevid1, evreid1) VALUES (NULL, 1, 1); INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, NULL, NULL); INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, NULL, 1); INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, 1, NULL); INSERT INTO event (evenid1, evevid1, evreid1) VALUES (1, 1, 1); SELECT * FROM event; id | evenid1 | evevid1 | evreid1 +-+-+- 2 | | | 1 3 | | 1 | 5 | 1 | | (3 rows) In 8.1, and in earlier versions if you create a cast from boolean to integer, you could do this: ALTER TABLE event ADD CONSTRAINT two_nulls_1 CHECK ( (evenid1 IS NOT NULL)::int + (evevid1 IS NOT NULL)::int + (evreid1 IS NOT NULL)::int = 1); This works because the cast converts true to 1 and false to 0; you're adding up the number of true expressions and requiring that the sum equal 1 (i.e., that exactly one expression be true). -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] My very first PL/pgSQL procedure...
you need to use EXECUTE to do the dynamic lock table.
sql = 'LOCK TABLE ' || sequence_name || 'IN ACCESS EXCLUSIVE MODE';
EXECUTE sql;
Thank you for your help ;-)
I've been able to rewrite my procedure as follows :
-
CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
current_seq integer;
BEGIN
EXECUTE 'LOCK TABLE ' || sequence_name || ' IN ACCESS EXCLUSIVE MODE';
current_seq := last_value FROM sequence_name;
IF current_seq minval THEN
EXECUTE 'ALTER SEQUENCE ' || sequence_name || ' RESTART WITH ' ||
minval;
END IF;
END;
$$ LANGUAGE plpgsql;
-
However, when I call : SELECT seq_min('seq_mytable', 1029);
I get this other error (translated from french) :
ERROR: «seq_mytable» is not a table
CONTEXT : SQL instruction «LOCK TABLE seq_mytable IN ACCESS EXCLUSIVE
MODE»
PL/pgSQL function seq_min line 4 at execute statement
So, it seems that it is impossible to lock a sequence !
If it is the case, how can I achieve the same result without locking the
sequence ?
Thank you again,
Philippe Ferreira.
---(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] Constraint that compares and limits field values
Michael Fuhr [EMAIL PROTECTED] wrote on 01/25/2006 10:25:38 AM: In 8.1, and in earlier versions if you create a cast from boolean to integer, you could do this: ALTER TABLE event ADD CONSTRAINT two_nulls_1 CHECK ( (evenid1 IS NOT NULL)::int + (evevid1 IS NOT NULL)::int + (evreid1 IS NOT NULL)::int = 1); This works because the cast converts true to 1 and false to 0; you're adding up the number of true expressions and requiring that the sum equal 1 (i.e., that exactly one _expression_ be true). -- Michael Fuhr I am in version 7.3 and it will not let me cast, I get message ERROR: Cannot cast type boolean to integer. I will save this for when I upgrade. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
[GENERAL] Trigger question: ROW or STATEMENT?
Attempting to do my first trigger and I'm confused about which FOR EACH I should use: ROW or STATEMENT. I import about 80K rows into an existing table each day. If I do a STATEMENT, will the changes only happen on the new 80K rows I inserted or will it be for all rows in the table - currently about 12M. TIA Patrick Hatcher ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Constraint that compares and limits field values
Martijn van Oosterhout kleptog@svana.org wrote on 01/25/2006 10:20:40 AM: On Wed, Jan 25, 2006 at 09:55:58AM -0800, [EMAIL PROTECTED] wrote: Parhaps something like: CHECK((CASE WHEN evenid1 IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN evenid2 IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN evenid3 IS NOT NULL THEN 1 ELSE 0 END)) = 1; This works with a few modifications... needed an extra ( ) enclosing entire statement. ALTER TABLE event ADD CONSTRAINT two_nulls_1 CHECK (((CASE WHEN evenid1 IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN evevid1 IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN evreid1 IS NOT NULL THEN 1 ELSE 0 END)) = 1); If you can find a function to turn a bool into an int it becomes even easier. I am in version 7.3 and it will not let me cast the boolean to an integer. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
Re: [GENERAL] Constraint that compares and limits field values
On Wed, Jan 25, 2006 at 10:51:23AM -0800, [EMAIL PROTECTED] wrote: I am in version 7.3 and it will not let me cast, I get message ERROR: Cannot cast type boolean to integer. I will save this for when I upgrade. You can create casts with CREATE CAST. http://www.postgresql.org/docs/7.3/static/sql-createcast.html Something like this should work: CREATE FUNCTION bool2int(boolean) RETURNS integer AS ' SELECT CASE WHEN $1 THEN 1 ELSE 0 END; ' LANGUAGE sql IMMUTABLE STRICT; CREATE CAST (boolean AS integer) WITH FUNCTION bool2int(boolean); -- Michael Fuhr ---(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] Postgresql Segfault in 8.1
OK, here's the output: (gdb) continue Continuing. Program received signal SIGSEGV, Segmentation fault. 0x0043c82c in heap_modifytuple () (gdb) // not very hopeful, I'd think // Can I get a RHES/CENTOS PG 8.1 RPM with the symbols enabled? Unfortunately, I don't think I can give out a dump of the DB (heavily constrained) because of private customer information... and the query works *FINE* with different datasets. There's something specific about THIS QUERY that's causing the failure. I'm going to try to get this to fail on another system that's not in production use, though it's a uniprocessor P4. -Ben On Wednesday 25 January 2006 07:52, you wrote: Benjamin Smith [EMAIL PROTECTED] writes: What's the best way to do this? Take PG down (normally started as a service) and run directly in a single-user mode? No, just start a psql session in one window, then in another window determine the PID of the backend process it's connected to, and attach gdb to that process. Something like ps auxww | grep postgres: ... eyeball determination of correct PID ... gdb /path/to/postgres-executable PID gdb continue Now, in the psql window, do what's needed to provoke the crash. gdb should trap at the instant of the segfault and give you another gdb prompt. Type bt to get the backtrace, then q to disconnect. regards, tom lane -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(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] Postgresql Segfault in 8.1
Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: ps auxww | grep postgres: ... eyeball determination of correct PID ... You can also do 'select pg_backend_pid();' from psql... I know that's there on 8.1, though I recall this was an 8.0 discussion and I *think* it's there too but not 100% sure. Good thought. I've been doing it via ps since forever, but pg_backend_pid is more foolproof (and it seems to have been in since 7.3, so that's not a problem). regards, tom lane ---(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] Postgresql Segfault in 8.1
Benjamin Smith [EMAIL PROTECTED] writes: OK, here's the output: (gdb) continue Continuing. Program received signal SIGSEGV, Segmentation fault. 0x0043c82c in heap_modifytuple () (gdb) // not very hopeful, I'd think // You forgot the bt part ... although I'm not sure we'd learn a whole lot more without debug symbols. Can I get a RHES/CENTOS PG 8.1 RPM with the symbols enabled? Current Red Hat practice is to put the debug symbols into separate debuginfo RPMs. Hopefully you can find the debuginfo RPM wherever you got the postgres RPM from. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger question: ROW or STATEMENT?
Patrick Hatcher [EMAIL PROTECTED] writes: Attempting to do my first trigger and I'm confused about which FOR EACH I should use: ROW or STATEMENT. I import about 80K rows into an existing table each day. If I do a STATEMENT, will the changes only happen on the new 80K rows I inserted or will it be for all rows in the table - currently about 12M. If you told us what you want the trigger to do it would probably be helpful. -Doug ---(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] Postgresql Segfault in 8.1
Tom, Since we host customer data, I have to get OK from the company attorney before I can give you a full howto create. I've been unable to recreate it without a full database dump. I'm waiting for a call back on that. I also can't recreate it on IA32. I tried to replicate the issue on a uniproc P4/32, but it worked fine there, so it does seem to be something specific about the fact that it's either X86/64 or that it's dual proc. The production server has 4GB of ECC RAM. I can consistently create the problem by dumping and reloading the database to a different PG database, and running it there, so AFAICT I'm not bugging anybody when I run this query. In the meantime, I found the debuginfo rpm, and installed it without a hitch. Luckily, it seems to take effect without having to restart the PG daemon. (which is busy serving 10-20 people at any given moment...) Again, here's the output from gdb. This looks a bit more useful, I hope this helps! Program received signal SIGSEGV, Segmentation fault. slot_deform_tuple (slot=0xa669c8, natts=36) at heaptuple.c:1262 1262off = att_addlength(off, thisatt-attlen, tp + off); (gdb) bt #0 slot_deform_tuple (slot=0xa669c8, natts=36) at heaptuple.c:1262 #1 0x0043c8f5 in slot_getattr (slot=0xa669c8, attnum=36, isnull=0x7fbfffde87 ) at heaptuple.c:1367 #2 0x0047a50a in FormIndexDatum (indexInfo=0xa66b60, slot=0xa669c8, estate=0xa61190, values=0x7fbfffdf10, isnull=0x7fbfffdef0 ) at index.c:962 #3 0x004ebee3 in ExecInsertIndexTuples (slot=0xa669c8, tupleid=0xa6efc4, estate=0xa61190, is_vacuum=0 '\0') at execUtils.c:925 #4 0x004e5265 in ExecutorRun (queryDesc=Variable queryDesc is not available. ) at execMain.c:1437 #5 0x00564312 in ProcessQuery (parsetree=Variable parsetree is not available. ) at pquery.c:174 #6 0x00565287 in PortalRun (portal=0xa5ed70, count=9223372036854775807, dest=0xa596f8, altdest=0xa596f8, completionTag=0x7fbfffe380 ) at pquery.c:1076 #7 0x00560f8b in exec_simple_query ( query_string=0xa440e0 INSERT INTO lcclasses (id, schoolyear, modified, entrydate, creator, status, name, location, city, maxclasssize, prerequisites, cost, costnote, coursecode, section, credits, whytake, materialsnote, te...) at postgres.c:1014 #8 0x00562e0e in PostgresMain (argc=4, argv=0xa0cca0, username=0xa0cc60 cworksdev) at postgres.c:3168 #9 0x0053d316 in ServerLoop () at postmaster.c:2852 #10 0x0053ea59 in PostmasterMain (argc=5, argv=0x9ea510) at postmaster.c:943 #11 0x005033c3 in main (argc=5, argv=0x9ea510) at main.c:256 (gdb) continue Continuing. Program terminated with signal SIGSEGV, Segmentation fault. The program no longer exists. ## Postgresql.conf listen_addresses = '127.0.0.1' port = 5432 max_connections = 96 shared_buffers=25 temp_buffers = 1 max_prepared_transactions = 0 work_mem = 1024 # min 64, size in KB maintenance_work_mem = 16384# min 1024, size in KB max_stack_depth = 9240 redirect_stderr = on# Enable capturing of stderr into log log_directory = 'pg_log'# Directory where log files are written log_truncate_on_rotation = on # If on, any existing log file of the same log_rotation_age = 1440 # Automatic rotation of logfiles will log_rotation_size = 0 # Automatic rotation of logfiles will autovacuum = on autovacuum_naptime = 600 lc_messages = 'en_US.UTF-8' # locale for system error message lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting add_missing_from = on -Ben On Wednesday 25 January 2006 11:18, you wrote: Benjamin Smith [EMAIL PROTECTED] writes: OK, here's the output: (gdb) continue Continuing. Program received signal SIGSEGV, Segmentation fault. 0x0043c82c in heap_modifytuple () (gdb) // not very hopeful, I'd think // You forgot the bt part ... although I'm not sure we'd learn a whole lot more without debug symbols. Can I get a RHES/CENTOS PG 8.1 RPM with the symbols enabled? Current Red Hat practice is to put the debug symbols into separate debuginfo RPMs. Hopefully you can find the debuginfo RPM wherever you got the postgres RPM from. regards, tom lane -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 4: Have you searched our list archives?
Re: [GENERAL] Trigger question: ROW or STATEMENT?
Here is the trigger the way it is currently written. I add some additional
information from another table:
CREATE TRIGGER item_cost_trig
BEFORE INSERT
ON cdm.cdm_ddw_tran_item
FOR EACH ROW
EXECUTE PROCEDURE cdm.insert_cost_to_tranitem_sub();
CREATE OR REPLACE FUNCTION cdm.insert_cost_to_tranitem_sub()
RETURNS trigger AS
'DECLARE
varCost float8;
varOwned float8;
varDept int4;
varVend int4;
varMstyle int4;
BEGIN
IF NEW.appl_id IN (''MCOM'',''NET'') THEN
select into varCost, varOwned, varDept, varVend,varMstyle cost,owned,
dept, vend,mstyle
from public.flbasics where upc = NEW.item_upc limit 1;
IF FOUND THEN
NEW.cost :=varCost;
NEW.owned :=varOwned;
NEW.dept_id := varDept;
NEW.vend_id := varVend;
NEW.mkstyl := varMstyle;
ELSE
NEW.cost :=0;
NEW.owned :=0;
END IF;
ELSE
NEW.cost :=0;
NEW.owned :=0;
END IF;
RETURN NEW;
END;'
LANGUAGE 'plpgsql' VOLATILE;
Patrick Hatcher
Development Manager Analytics/MIO
Macys.com
415-422-1610
Doug McNaught
[EMAIL PROTECTED]
g To
Patrick Hatcher
01/25/06 11:45 AM [EMAIL PROTECTED]
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] Trigger question:
ROW or STATEMENT?
Patrick Hatcher [EMAIL PROTECTED] writes:
Attempting to do my first trigger and I'm confused about which FOR EACH I
should use: ROW or STATEMENT. I import about 80K rows into an existing
table each day. If I do a STATEMENT, will the changes only happen on the
new 80K rows I inserted or will it be for all rows in the table -
currently
about 12M.
If you told us what you want the trigger to do it would probably be
helpful.
-Doug
---(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] My very first PL/pgSQL procedure...
Hi,
The only solution I've found to get the same reliable result, but
without locking, is the dirty way (loops) :
-
CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
sequence_record RECORD;
BEGIN
-- Get the current sequence value :
FOR sequence_record IN EXECUTE 'SELECT last_value FROM ' ||
sequence_name LOOP
NULL;
END LOOP;
-- Loop to bring the sequence to (at least) minval :
WHILE sequence_record.last_value minval LOOP
-- Increment by 1 the sequence (and get the new value) :
FOR sequence_record IN EXECUTE 'SELECT nextval(''' || sequence_name
|| ''') AS last_value' LOOP
NULL;
END LOOP;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
-
It gives the result I expect (and it doesn't interfere with concurrent
uses of the sequence), but it is not very optimized !
So, if someone have a better idea, I'm still open !
Thank you,
Philippe Ferreira.
---(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] Postgresql Segfault in 8.1
Benjamin Smith [EMAIL PROTECTED] writes: Aha, yep. Sorry: Program received signal SIGSEGV, Segmentation fault. 0x0043c82c in heap_modifytuple () (gdb) bt #0 0x0043c82c in heap_modifytuple () #1 0x0043c8f5 in slot_getattr () #2 0x0047a50a in FormIndexDatum () #3 0x004ebee3 in ExecInsertIndexTuples () #4 0x004e5265 in ExecutorRun () #5 0x00564312 in FreeQueryDesc () #6 0x00565287 in PortalRun () #7 0x00560f8b in pg_parse_query () #8 0x00562e0e in PostgresMain () #9 0x0053d316 in ClosePostmasterPorts () #10 0x0053ea59 in PostmasterMain () #11 0x005033c3 in main () Oh, so this is happening during index entry creation? (The reference to heap_modifytuple is misleading, but in a debug-symbol-free backend it's not so surprising.) This suddenly looks a whole lot like a known bug: http://archives.postgresql.org/pgsql-hackers/2005-11/msg01016.php Which version did you say you were using exactly? That bug is fixed in 8.1.1 ... 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] Postgresql Segfault in 8.1
Version: postgresql-8.1.0-4.c4 I'll have to see about getting an update... Thanks a TON, -Ben On Wednesday 25 January 2006 13:11, you wrote: Benjamin Smith [EMAIL PROTECTED] writes: Aha, yep. Sorry: Program received signal SIGSEGV, Segmentation fault. 0x0043c82c in heap_modifytuple () (gdb) bt #0 0x0043c82c in heap_modifytuple () #1 0x0043c8f5 in slot_getattr () #2 0x0047a50a in FormIndexDatum () #3 0x004ebee3 in ExecInsertIndexTuples () #4 0x004e5265 in ExecutorRun () #5 0x00564312 in FreeQueryDesc () #6 0x00565287 in PortalRun () #7 0x00560f8b in pg_parse_query () #8 0x00562e0e in PostgresMain () #9 0x0053d316 in ClosePostmasterPorts () #10 0x0053ea59 in PostmasterMain () #11 0x005033c3 in main () Oh, so this is happening during index entry creation? (The reference to heap_modifytuple is misleading, but in a debug-symbol-free backend it's not so surprising.) This suddenly looks a whole lot like a known bug: http://archives.postgresql.org/pgsql-hackers/2005-11/msg01016.php Which version did you say you were using exactly? That bug is fixed in 8.1.1 ... regards, tom lane -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] user defined function
andrew [EMAIL PROTECTED] writes: Sorry, I modified the parser code and forgot abt it. Now there is no problem in creating the function. But there is another problem. I create a function to accept record type parameter. But when I call it on a specific composite type, error is reported. The followings are what I have done: backend create function complete(record) returns int4 as '$libdir/qualityudf' language C QUERY: create function complete(record) returns int4 as '$libdir/qualityudf' language C backend select *, complete(Person) from Person QUERY: select *, complete(Person) from Person ERROR: Function complete(person) does not exist Hmm. Looking at parse_coerce.c, 8.1 is the first release that thinks named composite types can be coerced to RECORD. I think you may be forced to upgrade if you want this to work. Changing 7.3's coerce_type() to allow this case would be simple enough, but I think you are still going to be minus a lot of infrastructure that's required to make it actually do anything useful :-( regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgresql Segfault in 8.1
Hello Ben, Unless I am wrong there is a set of RPMs available via the PostgreSQL site for 8.1.1/2 http://www.postgresql.org/ftp/binary/v8.1.2/linux/rpms/ Aly. Benjamin Smith wrote: Version: postgresql-8.1.0-4.c4 I'll have to see about getting an update... Thanks a TON, -Ben On Wednesday 25 January 2006 13:11, you wrote: Benjamin Smith [EMAIL PROTECTED] writes: Aha, yep. Sorry: Program received signal SIGSEGV, Segmentation fault. 0x0043c82c in heap_modifytuple () (gdb) bt #0 0x0043c82c in heap_modifytuple () #1 0x0043c8f5 in slot_getattr () #2 0x0047a50a in FormIndexDatum () #3 0x004ebee3 in ExecInsertIndexTuples () #4 0x004e5265 in ExecutorRun () #5 0x00564312 in FreeQueryDesc () #6 0x00565287 in PortalRun () #7 0x00560f8b in pg_parse_query () #8 0x00562e0e in PostgresMain () #9 0x0053d316 in ClosePostmasterPorts () #10 0x0053ea59 in PostmasterMain () #11 0x005033c3 in main () Oh, so this is happening during index entry creation? (The reference to heap_modifytuple is misleading, but in a debug-symbol-free backend it's not so surprising.) This suddenly looks a whole lot like a known bug: http://archives.postgresql.org/pgsql-hackers/2005-11/msg01016.php Which version did you say you were using exactly? That bug is fixed in 8.1.1 ... regards, tom lane -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(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] Trigger question: ROW or STATEMENT?
Patrick Hatcher [EMAIL PROTECTED] writes: Here is the trigger the way it is currently written. I add some additional information from another table: If you're modifying each row before it goes in, it should definitely be a FOR EACH ROW trigger. -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] My very first PL/pgSQL procedure...
why not just use setval(), see docs for arguments.
-- Original Message ---
From: Philippe Ferreira [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Wed, 25 Jan 2006 22:11:11 +0100
Subject: Re: [GENERAL] My very first PL/pgSQL procedure...
Hi,
The only solution I've found to get the same reliable result, but
without locking, is the dirty way (loops) :
-
CREATE OR REPLACE FUNCTION seq_min(sequence_name varchar, minval
integer) RETURNS VOID AS $$
DECLARE
sequence_record RECORD;
BEGIN
-- Get the current sequence value :
FOR sequence_record IN EXECUTE 'SELECT last_value FROM ' ||
sequence_name LOOP
NULL;
END LOOP;
-- Loop to bring the sequence to (at least) minval :
WHILE sequence_record.last_value minval LOOP
-- Increment by 1 the sequence (and get the new value) :
FOR sequence_record IN EXECUTE 'SELECT nextval(''' || sequence_name
|| ''') AS last_value' LOOP
NULL;
END LOOP;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
-
It gives the result I expect (and it doesn't interfere with concurrent
uses of the sequence), but it is not very optimized !
So, if someone have a better idea, I'm still open !
Thank you,
Philippe Ferreira.
---(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 Original Message ---
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Trigger question: ROW or STATEMENT?
Would I gain any advantage by changing to it to fire after the insert? thanks again for the help Patrick Hatcher Development Manager Analytics/MIO Macys.com 415-422-1610 Doug McNaught [EMAIL PROTECTED] g To Patrick Hatcher 01/25/06 01:36 PM [EMAIL PROTECTED] cc pgsql-general@postgresql.org Subject Re: [GENERAL] Trigger question: ROW or STATEMENT? Patrick Hatcher [EMAIL PROTECTED] writes: Here is the trigger the way it is currently written. I add some additional information from another table: If you're modifying each row before it goes in, it should definitely be a FOR EACH ROW trigger. -Doug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] pgstattuple output?
Hi, I have an aging 7.3 database on Solaris 9/Sparc. We are on the verge of upgrading Postgresql, but we first need to reclaim some disk space. I was looking for tables that may have become fragmented when I saw this pgstattuple output that confused me: table_len 21773516800 tuple_count 69244287 tuple_len 13058755529 tuple_percent 59.98 dead_tuple_count0 dead_tuple_len 0 dead_tuple_percent 0 free_space 8013437220 free_percent 36.8 Why is there so much free space with no dead tuples? This table has likely had serveral columns added over time, is that part of the problem? Indexes? Thanks, Michael ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Implimenting an XPath engine in PostgreSQL / Staircase Join
I ran across http://www.vldb.org/conf/2004/DEMP14.PDF while googling for something else; I haven't seen this before so I thought I'd post it. Anyone dealing with XML data should take a look, though it might have other uses too. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] xml_valid function
Not sure what the correct forum for pgxml/xml2 questions is. I was wondering what is the definition of valid that the xml_valid(text) function that is part of that module uses? It seems different from the W3C definition of valid XML (is there an implicit DTD?) Maybe it is more akin to well-formed? George ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pgstattuple output?
On Wed, Jan 25, 2006 at 03:40:38PM -0800, Michael Crozier wrote:
Why is there so much free space with no dead tuples? This table has likely
had serveral columns added over time, is that part of the problem? Indexes?
An ordinary vacuum frees space for PostgreSQL's use but it doesn't
shrink the table's file(s) and return space to the operating system;
this remains true in the latest versions. If the table was ever
as large as you're seeing then it won't shrink unless you do a
vacuum full, cluster, dump/drop/create/restore, etc. Here's an
example in 8.1.2:
test= CREATE TABLE foo (x integer);
CREATE TABLE
test= INSERT INTO foo SELECT 1 FROM generate_series(1, 10);
INSERT 0 10
test= UPDATE foo SET x = 2;
UPDATE 10
test= VACUUM foo;
VACUUM
test= \x
Expanded display is on.
test= SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]--+
table_len | 7225344
tuple_count| 10
tuple_len | 320
tuple_percent | 44.29
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 3205936
free_percent | 44.37
This example shows the same thing you're seeing: lots of free space
but no dead tuples. The table *did* have a lot of dead tuples due
to the update but the vacuum freed that space. If we do another
update the table length doesn't change (not by much, anyway) because
the database is able to reuse the free space without having to
allocate more pages via the operating system:
test= UPDATE foo SET x = 3;
UPDATE 10
test= SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]--+
table_len | 7233536
tuple_count| 10
tuple_len | 320
tuple_percent | 44.24
dead_tuple_count | 10
dead_tuple_len | 320
dead_tuple_percent | 44.24
free_space | 12348
free_percent | 0.17
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] pgstattuple output?
Why is there so much free space with no dead tuples? This table has likely had serveral columns added over time, is that part of the problem? Indexes? An ordinary vacuum frees space for PostgreSQL's use but it doesn't shrink the table's file(s) and return space to the operating system; this remains true in the latest versions. I think I see now, dead tuples are the tuples that have yet to be reclaimed by vacuum, not tuples that are ready to be used. I'm still rather confused, as this table is only modified via inserts. No deletes or update operations are ever performed. Logically (ie I don't really know the truth) this table should have no free tuples or free space except for the remainder of the last allocated page. What I need to see is the free space map, but I don't think those patches/functions exist for 7.3. I'll go look around. Thanks, Michael ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Wiki Site for the PostgreSQL Newbie
I have started a wiki site for the PostgreSQL newbie. I'm actually a noob myself, but I thought I would take the time to document my mistakes and also provide some tips that I hope will help others. I welcome suggestions and correcions from more experience PostgreSQL and pgAdmin 3 users. I hope the wiki site will be a benefit to others. Scott Huey (P.S. - There isn't much content on the wiki just yet, but I'll be working on that.) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Wiki Site for the PostgreSQL Newbie
That earlier e-mail won't do anyone much good with out the link to the site. Sorry. That's what happens when I work late in the middle of the week. Link: http://www.bluwiki.org/go/PostgreSQL_Tips_From_The_Sunburned_Surveyor Scott Huey On 1/25/06, Redefined Horizons [EMAIL PROTECTED] wrote: I have started a wiki site for the PostgreSQL newbie. I'm actually a noob myself, but I thought I would take the time to document my mistakes and also provide some tips that I hope will help others. I welcome suggestions and correcions from more experience PostgreSQL and pgAdmin 3 users. I hope the wiki site will be a benefit to others. Scott Huey (P.S. - There isn't much content on the wiki just yet, but I'll be working on that.) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Trigger question: ROW or STATEMENT?
On Wed, Jan 25, 2006 at 02:47:45PM -0800, Patrick Hatcher wrote: Would I gain any advantage by changing to it to fire after the insert? If you're modifying the row then the trigger must fire before the insert. An after trigger can abort the operation by raising an error and it can perform actions like updating another table, but by the time an after trigger fires it's too late to change the current row (except via an UPDATE, and then you must beware of cascading triggers leading to infinite recursion). You might want to read Overview of Trigger Behavior in the documentation -- it describes the various kinds of triggers (row/statement and before/after) and when certain types are appropriate: http://www.postgresql.org/docs/8.1/interactive/triggers.html#TRIGGER-DEFINITION The documentation mentions that if you have no specific reason to use before or after, then before is more efficient. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Which table(s) in the catalog define the tables and views?
I'm want to write a query to list definitions of all the fields in my tables and which table it belongs to. And similarly for views and tables. This is so I can check for table and column name consistency, extra tables and column type consistency. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Which table(s) in the catalog define the tables and views?
On Jan 26, 2006, at 15:17 , Chris Velevitch wrote: I'm want to write a query to list definitions of all the fields in my tables and which table it belongs to. And similarly for views and tables. This is so I can check for table and column name consistency, extra tables and column type consistency. It's not a query, but will pg_dump -s do what you want?[1] Else you can take a look at the INFORMATION_SCHEMA[2] or the system catalogs[3]. [1](http://www.postgresql.org/docs/current/interactive/app-pgdump.html) [2](http://www.postgresql.org/docs/current/interactive/information- schema.html) [3](http://www.postgresql.org/docs/current/interactive/catalogs.html) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Initdb panic: invalid record offset at 0/0 creating
Tom, finally I tried with only this option CC=icc, as there is an another error (error: Could not open %files file /home/postdev/BUILD/postgresql-8.1.1/debugfiles.list), I dont know where this debugfiles.list coming from. I prefered to stop for now , I'll will try later, so I have used gcc and it is fine ! (compiling, installing, initdb,) regards Agnès Tom Lane wrote: Agnes Bocchino [EMAIL PROTECTED] writes: Do you have some explanation or/and tips on how to build a successfull rpm on ia64, with icc, perhaps we shoud not please tell us Perhaps you should use gcc? I don't personally have the time or interest to dig into this. Considering that PG works fine on several other 64-bit platforms, it seems unlikely (though not impossible of course) that this is our bug. What could be happening is that the RPM packaging involves tools that aren't compatible with icc. I think you already found that out with regard to brp-strip, and there may be some more-subtle problems too. You might try forgetting about RPM entirely and just building from the source tarball with configure CC=icc plus whatever other options you want. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
