Re: [GENERAL] standard LOB support
Thomas, Thank you for your comment. I found that using getBinaryStream(), setBinaryStream(), getCharacterStream() and setCharacterStream() to handle LOBs across different DBMS is much more portable (and reliably) than using the Clob()/Blob() methods. According to JDBC 3.0 specifiction, those 4 methods may not be compatible to BLOB/CLOB. Some databases may support them to access LOB data but not all databases. But my target databases are, actually, only PostgreSQL, Oracle and DB2 and there is no problem with PostgreSQL. Also, according to the Oracle JDBC driver manual, Oracle supports stream access to LOB through the 4 methods. I'll also try DB2 soon. Thanks, ebi ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] standard LOB support
Hi, I found that using getBinaryStream(), setBinaryStream(), getCharacterStream() and setCharacterStream() to handle LOBs across different DBMS is much more portable (and reliably) than using the Clob()/Blob() methods. According to JDBC 3.0 specifiction, those 4 methods may not be compatible to BLOB/CLOB. Some databases may support them to access LOB data but not all databases. But my target databases are, actually, only PostgreSQL, Oracle and DB2 and there is no problem with PostgreSQL. Also, according to the Oracle JDBC driver manual, Oracle supports stream access to LOB through the 4 methods. I'll also try DB2 soon. DB2 is ok too! According to manuals, both of Oracle and DB2 support getBytes()/setBytes()/getBinaryStream()/setBinaryStream() for BLOB and getString()/setString()/getCharacterStream()/setCharacterStream() for CLOB. Therefore, I can develop portable JDBC applications with LOB by using those methods and bytea/text data types on PostgreSQL. In addition, I can use DDL scripts including BLOB/CLOB with PostgreSQL too if I define domains as follows. CREATE DOMAIN BLOB AS BYTEA; CREATE DOMAIN CLOB AS TEXT; Tom, I also say thank you to you. But contrib/lo looks a little too much to me for this time. Thanks, ebi ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] standard LOB support
EBIHARA, Yuichiro wrote on 22.06.2007 08:34: Thomas, Thank you for your comment. I found that using getBinaryStream(), setBinaryStream(), getCharacterStream() and setCharacterStream() to handle LOBs across different DBMS is much more portable (and reliably) than using the Clob()/Blob() methods. According to JDBC 3.0 specifiction, those 4 methods may not be compatible to BLOB/CLOB. Some databases may support them to access LOB data but not all databases. Hmm. At least for updating LOBs, my method should be legal. This is a quote from jdbc-3_0-fr-spec.pdf The setBinaryStream and setObject methods may also be used to set a Blob object as a parameter in a PreparedStatement object. The setAsciiStream, setCharacterStream, and setObject methods are alternate means of setting a Clob object as a parameter. But I have to admit that I never read the specs in detail until now. Those methods were simply working fine (and were the only reliable way to handle LOBs with the Oracle drivers). Btw: these methods are working (for me) with Oracle, SQL Server (jTDS and MS Driver), DB2 (8.x), Firebird, Derby, MySQL, HSQL, H2, Informix and Sybase Adaptive Server Anywhere. But I do think that the exception thrown when using getClob() or getBlob() is an error in the JDBC driver. Maybe we should file an issue for this. Regards Thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] standard LOB support
Thomas, According to JDBC 3.0 specifiction, those 4 methods may not be compatible to BLOB/CLOB. Some databases may support them to access LOB data but not all databases. Hmm. At least for updating LOBs, my method should be legal. This is a quote from jdbc-3_0-fr-spec.pdf The setBinaryStream and setObject methods may also be used to set a Blob object as a parameter in a PreparedStatement object. The setAsciiStream, setCharacterStream, and setObject methods are alternate means of setting a Clob object as a parameter. Sorry, my comment was partially incorrect. See B-182(TABLE B-6) of the spec. getBinaryStream()/getCharacterStream are not compatible to LOB. Also, there were some limitations with Oracle JDBC Thin driver 9.2 and those methods didn't work with LOB. Thanks, ebi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Can I backup/restore a database in a sql script?
Hi, I want to write a sql script in which I backup a database and restore a new (altered) version of that database. Is that possible? If so , can anyone give me an example of how to do that? I can run it from any command prompt (psql -U postgres template1 my_db.backup)but I would like it to run from psql (which should give me 1 script for all platforms I must do this: Windows, FreeBSD and Debian) TIA Joost ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] standard LOB support
EBIHARA, Yuichiro wrote on 22.06.2007 09:28: Hmm. At least for updating LOBs, my method should be legal. This is a quote from jdbc-3_0-fr-spec.pdf The setBinaryStream and setObject methods may also be used to set a Blob object as a parameter in a PreparedStatement object. The setAsciiStream, setCharacterStream, and setObject methods are alternate means of setting a Clob object as a parameter. Sorry, my comment was partially incorrect. See B-182(TABLE B-6) of the spec. getBinaryStream()/getCharacterStream are not compatible to LOB. Thanks for the pointer ;) According to that table, the PG driver is actually correct, as bytea is reported as Types.BINARY not Types.BLOB Also, there were some limitations with Oracle JDBC Thin driver 9.2 and those methods didn't work with LOB. Yes I found that as well. Only the 10.x driver work correctly Regards Thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] standard LOB support
Finally, I'd like to bring up my first question again. Is there any plan to support BLOB and CLOB in future releases? Don't you guys need a standard LOB feature? I no longer need it, though ;-p With the current large objects feature, I don't think it's not difficult to support it... Thanks, ebi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] standard LOB support
Thomas Kellerer wrote: Hmm. At least for updating LOBs, my method should be legal. This is a quote from jdbc-3_0-fr-spec.pdf The setBinaryStream and setObject methods may also be used to set a Blob object as a parameter in a PreparedStatement object. The setAsciiStream, setCharacterStream, and setObject methods are alternate means of setting a Clob object as a parameter. But I have to admit that I never read the specs in detail until now. Those methods were simply working fine (and were the only reliable way to handle LOBs with the Oracle drivers). Yuichiro, I'd use these methods for BLOBs if they work on all the DBMS Thomas mentioned. But I do think that the exception thrown when using getClob() or getBlob() is an error in the JDBC driver. Maybe we should file an issue for this. I'm certainly not a core developer of the JDBC provider, but working with it and reading the code it seems quite clear to me that the driver treats PostgreSQL large objects as java.sql.BLOBs and byteas as java.sql.Types.BINARY, and that this is intentional. I don't think it would be a simple change to allow byteas to be treated as BLOBs. I have CC'ed the JDBC mailing list as I think this should go there (too). Yours, Laurenz Albe ---(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] Can I backup/restore a database in a sql script?
Joost Kraaijeveld wrote: Hi, I want to write a sql script in which I backup a database and restore a new (altered) version of that database. Is that possible? If so , can anyone give me an example of how to do that? I can run it from any command prompt (psql -U postgres template1 my_db.backup)but I would like it to run from psql (which should give me 1 script for all platforms I must do this: Windows, FreeBSD and Debian) You can restore using just psql (because pg_dump can produce an sql file to be processed). I don't know of any way to backup a database that doesn't use pg_dump. -- Richard Huxton Archonet Ltd ---(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] Regression - Query requires full scan, GIN doesn't support it
Is this a permanent limitation of GIN, or is a fix possible? Permanent. You could check user input by querytree() function --- if it returns 'T' string then fullscan will be needed. If your tsquery is produced by plainto_tsquery() call then it will not find any result, so you can show to user void page. Is a fix being worked on? If a fix is forthcoming, will it be available in the 8.2 series or only 8.3+? Possibly, full fix in 8.4. But I will not promise. 8.3 will have protection from queries which doesn't match anything. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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] Regular express question
I have a table that consists of a set of regular expressions, a priority and a result. I need to be able to match field in another table against the set of regular expressions (ordered by priority) and use the first result. Reading the documentation I can see how to put the regular expression into an SQL statement (that is as text or a ? which my code provides) but I can not see how to get the expression from the table. David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Regular express question
On Friday 22 June 2007, David Goodenough wrote: I have a table that consists of a set of regular expressions, a priority and a result. I need to be able to match field in another table against the set of regular expressions (ordered by priority) and use the first result. Reading the documentation I can see how to put the regular expression into an SQL statement (that is as text or a ? which my code provides) but I can not see how to get the expression from the table. David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster OK, I worked it out for myself. Of course I can put a field name on the right hand side of the SIMILAR TO and the ? on the left had side, then it works just as you would expect. So:- select result from rules where ? similar to rule order by priority limit 1 gives me the answer I want. David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Proposed Feature
I'm using PG on windows for the first time (as of about 6 minutes ago). I was thinking that it would be great to have a system tray icon with a running indicator, kind of like the way Apache2.x for windows has, or even MSSQL. Perhaps the PG logo with a small white circle with a red square or a green triangle in the same fashion. Just a thought. - Naz. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to install Postgresql on MS Vista?
At 01:58 AM 6/22/2007, dfx wrote: I tryied it but get errors on create user postgres. Is there some workaround? Upgrade to Windows XP SP2? Or Win2K? Regards, Link. ---(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] How to install Postgresql on MS Vista?
On 6/21/07, Lee Keel [EMAIL PROTECTED] wrote: The first thing you have to do is disable the User Access Control. No you don't, actually. Just start the installer from an elevated command prompt (Right-click on Command Prompt in the start menu and choose Run As Administrator, change to the installer directory and 'start whatever.msi'). DJ ---(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] Regular express question
David Goodenough wrote: I have a table that consists of a set of regular expressions, a priority and a result. I need to be able to match field in another table against the set of regular expressions (ordered by priority) and use the first result. Reading the documentation I can see how to put the regular expression into an SQL statement (that is as text or a ? which my code provides) but I can not see how to get the expression from the table. The only way I can see is to write a function in - say - PL/pgSQL. Yours, Laurenz Albe ---(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] How to install Postgresql on MS Vista?
D.J. Heap wrote: On 6/21/07, Lee Keel [EMAIL PROTECTED] wrote: The first thing you have to do is disable the User Access Control. No you don't, actually. Just start the installer from an elevated command prompt (Right-click on Command Prompt in the start menu and choose Run As Administrator, change to the installer directory and 'start whatever.msi'). Except the administrator account is disabled by default on Vista. Regards, Dave ---(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] How to install Postgresql on MS Vista?
Dave Page wrote: D.J. Heap wrote: On 6/21/07, Lee Keel [EMAIL PROTECTED] wrote: The first thing you have to do is disable the User Access Control. No you don't, actually. Just start the installer from an elevated command prompt (Right-click on Command Prompt in the start menu and choose Run As Administrator, change to the installer directory and 'start whatever.msi'). Except the administrator account is disabled by default on Vista. Huh? What I am confused about, and I don't pretend in anyway to understand Vista but I just bought my wife a new vista machine and the default user (the one created during setup) was an Administrator. Joshua D. Drake Regards, Dave ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to install Postgresql on MS Vista?
On 6/22/07, Dave Page [EMAIL PROTECTED] wrote: [snip] Except the administrator account is disabled by default on Vista. What do you mean? If you logon as an administrator then by default Vista drops a lot of priviledges, but you can get them back to execute a program that requires them by using the 'Run as administrator' right-click menu item (which is on the right-click menu for most things you can run, but not .msi's which is why you have to run them from an elevated command prompt). AFAIK, all the 'Run as administrator' menu item does is give you back your full admin rights if you are an admin (or else it will ask you for an admin user and password) and then run the program in that context. In any case, using an elevated command prompt has worked fine for me with the postgres 8.2.4 installer on 2 different Vista machines and has worked for other msi's that otherwise fail as well. Does it not work for you? DJ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [PGSQL 8.2.x] INSERT+INSERT
On Thursday 21 June 2007 15:47:17 Dawid Kuroczko wrote:
On 6/21/07, Vincenzo Romano [EMAIL PROTECTED] wrote:
Hi all.
I'd like to do the following:
insert into t1
values (
'atextvalue',(
insert into t2
values ( 'somethingelse' )
returning theserial
)
)
;
that is, I first insert data into t2 getting back the newly
created serial values, then i insert this values in another
table. I get an error message:
ERROR: syntax error at or near into
referring to thwe second inner into.
Is there a way to do this?
The inner insert...returning should be the expression to be
used in the outer insert.
My objective iDawid Kuroczko [EMAIL PROTECTED]s to create an
SQL script to load some 20+ million
records and avoiding function calls would save some time.
I'm afraid INSERT ... RETURNING cannot be used where a (sub)select
could be. It returns data to the calling application only.
I think it would be greatly helpful if the insert...returning could be
seen as a select statement and, thus, being usable in the way I
have described. I suspect that the insert...returning is actually
implemented as an inser plus a select.
Given tables:
qnex=# CREATE TABLE t1 (t text, id int);
qnex=# CREATE TABLE t2 (id serial, sth text);
NOTICE: CREATE TABLE will create implicit sequence t2_id_seq for
serial column t2.id
You want to:
qnex=# INSERT INTO t2 (sth) VALUES ('somethingelse');
INSERT 0 1
qnex=# INSERT INTO t1 VALUES ('atextvalue', currval('t2_id_seq'));
INSERT 0 1
This works only when you execute statements one by one like you did.
Or wrap it around SQL function:
That's the way I'm doind now even if in a slightly different way.
20+ million calls will badly slooow down the DB insertions and require
you to know the exact name of the implicit sequence and, more
important, not to have any concurrent accesses to it.
--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to install Postgresql on MS Vista?
D.J. Heap wrote: On 6/22/07, Dave Page [EMAIL PROTECTED] wrote: [snip] Except the administrator account is disabled by default on Vista. What do you mean? Exactly what I wrote. By default, the .\Administrator account is disabled on Vista so you cannot login to it, or runas it. It's easy to re-enable from the Users and Groups MMC snapin of course. If you logon as an administrator then by default Vista drops a lot of priviledges, but you can get them back to execute a program that requires them by using the 'Run as administrator' right-click menu item (which is on the right-click menu for most things you can run, but not .msi's which is why you have to run them from an elevated command prompt). AFAIK, all the 'Run as administrator' menu item does is give you back your full admin rights if you are an admin (or else it will ask you for an admin user and password) and then run the program in that context. In any case, using an elevated command prompt has worked fine for me with the postgres 8.2.4 installer on 2 different Vista machines and has worked for other msi's that otherwise fail as well. Does it not work for you? I'm not saying what you suggest won't work, only that you cannot do it on an out-of-the-box install. For the record, I've updated the installer for 8.3 to properly work with UAC. Regards, Dave. ---(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] Function call costs for SQL and PLPgSQL
Vincenzo Romano [EMAIL PROTECTED] writes: I need to create a huge SQL script to load 20+ M rows in a DB. I've been force to wrap the inserts into PG functions. I can write them in either SQl or PLPgSQL but don't know whether this can make a big difference as far as time of overall execution is concerned. Somewhere else I've been told that SQL function bodies get inlined during execution. Is thus correct to consider SQL functions faster that the conterpart written in PLPgSQL? Inlining only applies to simple-SELECT SQL functions (ie, pure functions). For what you're trying to do, I think plpgsql will be faster as well as more flexible. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to install Postgresql on MS Vista?
Joshua D. Drake wrote: BTW, and this is completely off topic but Vista really doesn't seem nearly as bad as all the geeks would make it out to be. It seems a nice evolutionary step ... although it seems a step toward MacOSX ;) Oh it's certainly got nice eye candy, and is quite easy for the newbie to use, but UAC is a pita (I refer you back to the ECPG update regression check hoohaa). Maybe it's because I use a Mac 50% of the time though... /D ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Function call costs for SQL and PLPgSQL
Hi all. I need to create a huge SQL script to load 20+ M rows in a DB. I've been force to wrap the inserts into PG functions. I can write them in either SQl or PLPgSQL but don't know whether this can make a big difference as far as time of overall execution is concerned. Somewhere else I've been told that SQL function bodies get inlined during execution. Is thus correct to consider SQL functions faster that the conterpart written in PLPgSQL? Thanks. -- Vincenzo Romano -- Maybe Computer will never become as intelligent as Humans. For sure they won't ever become so stupid. [VR-1988] ---(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] Proposed Feature
Naz Gassiep wrote: I'm using PG on windows for the first time (as of about 6 minutes ago). I was thinking that it would be great to have a system tray icon with a running indicator, kind of like the way Apache2.x for windows has, or even MSSQL. Perhaps the PG logo with a small white circle with a red square or a green triangle in the same fashion. Just a thought. And what does the icon show or do? -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Proposed Feature
Bruce Momjian wrote: Naz Gassiep wrote: I'm using PG on windows for the first time (as of about 6 minutes ago). I was thinking that it would be great to have a system tray icon with a running indicator, kind of like the way Apache2.x for windows has, or even MSSQL. Perhaps the PG logo with a small white circle with a red square or a green triangle in the same fashion. Just a thought. And what does the icon show or do? We had this for the win32 version of replicator. Ours allowed: Viewing Eventlog Start/Stop Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [PGSQL 8.2.x] INSERT+INSERT
I think it would be greatly helpful if the insert...returning could be seen as a select statement and, thus, being usable in the way I have described. I suspect that the insert...returning is actually implemented as an inser plus a select. You can create a function which does the INSERT RETURNING and returns the result, and use this function in a subsequent INSERT. You can also write, in plpgsql : FOR a,b,c IN SELECT ... LOOP INSERT INTO table1 (...) VALUES (a,b) RETURNING your_id INTO _var; INSERT INTO table2 (...) VALUES (c , _var ); END LOOP ---(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] How to install Postgresql on MS Vista?
On 6/22/07, Dave Page [EMAIL PROTECTED] wrote: [snip] Exactly what I wrote. By default, the .\Administrator account is disabled on Vista so you cannot login to it, or runas it. It's easy to re-enable from the Users and Groups MMC snapin of course. Ah yes, but I don't recall doing anything special during Vista installation and it still made my personal user an administrator so I have never needed to use 'the' administrator account. Perhaps I told it to make me an admin during installation, but I don't think so. [snip] I'm not saying what you suggest won't work, only that you cannot do it on an out-of-the-box install. That has not been my experience, but as I said above, perhaps I told it to make me an admin during installation -- I don't recall for sure. For the record, I've updated the installer for 8.3 to properly work with UAC. Great, thank you! DJ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to install Postgresql on MS Vista?
Dave Page wrote: Joshua D. Drake wrote: Dave Page wrote: Except the administrator account is disabled by default on Vista. Huh? What I am confused about, and I don't pretend in anyway to understand Vista but I just bought my wife a new vista machine and the default user (the one created during setup) was an Administrator. *An* administrator, not *the* administrator. Think of it in terms of root being disabled, with your wife having 'sudo nearly anything' access. O.k. that makes much more sense. Thanks. BTW, and this is completely off topic but Vista really doesn't seem nearly as bad as all the geeks would make it out to be. It seems a nice evolutionary step ... although it seems a step toward MacOSX ;) Joshua D. Drake Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] [PGSQL 8.2.x] INSERT+INSERT
Vincenzo Romano [EMAIL PROTECTED] writes: Or wrap it around SQL function: That's the way I'm doind now even if in a slightly different way. 20+ million calls will badly slooow down the DB insertions Are you sure? How much? and require you to know the exact name of the implicit sequence If you're sure you only have a single sequence being bumped here (no triggers inserting somewhere else, or other columns with sequences for default values, etc) then you could use lastval(). and, more important, not to have any concurrent accesses to it. huh? no, sequences don't require this. They would be pointless if they did. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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] Optimizer problem in 8.1.6
Maybe this is already solved in more advanced releases, but just in case. VIEW active_users: SELECT * FROM users WHERE active AND ((field IS NULL) OR (NOT field)); Table users has index on text field login. EXPLAIN SELECT * from active_users where login='xxx'; QUERY PLAN -- Index Scan using active_users on users u (cost=0.00..5.97 rows=1 width=131) Index Cond: (login = 'xxx'::text) Filter: (active AND ((field1 IS NULL) OR (NOT field1))) So far, everything OK. Now, combined (sorry for the convoluted query, it is build automatically by an app). EXPLAIN SELECT DISTINCT p.id FROM partes_tecnicos p, rel_usr_sector_parte_tecnico r, active_users u WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id AND u.login='xxx' AND r.id_sector=p.id_sector_actual AND p.id_cola_por_ambito=1) OR p.id_cola_por_ambito=1) AND p.id_situacion!=6; - Unique (cost=1016.84..22057814.97 rows=219 width=4) - Nested Loop (cost=1016.84..19607287.64 rows=980210931 width=4) - Nested Loop (cost=8.07..2060.25 rows=100959 width=4) - Index Scan using partes_tecnicos_pkey on partes_tecnicos p (cost=0.00..33.00 rows=219 width=4) Filter: ((id_cola_por_ambito = 1) AND (id_situacion 6)) - Materialize (cost=8.07..12.68 rows=461 width=0) - Seq Scan on rel_usr_sector_parte_tecnico r (cost=0.00..7.61 rows=461 width=0) - Materialize (cost=1008.77..1105.86 rows=9709 width=0) - Seq Scan on users u (cost=0.00..999.06 rows=9709 width=0) Filter: (active AND ((field1 IS NULL) OR (NOT field1))) Notice the seq. scan on users. It is solved using: EXPLAIN SELECT DISTINCT p.id FROM partes_tecnicos p, pt.rel_usr_sector_parte_tecnico r, (SELECT id FROM active_users WHERE login='xxx') u WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id AND r.id_sector=p.id_sector_actual AND p.id_cola_por_ambito=1) OR p.id_cola_por_ambito=1 ) AND p.id_situacion!=6; - Unique (cost=18.65..2323.23 rows=219 width=4) - Nested Loop (cost=18.65..2070.83 rows=100959 width=4) - Index Scan using partes_tecnicos_pkey on partes_tecnicos p (cost=0.00..33.00 rows=219 width=4) Filter: ((id_cola_por_ambito = 1) AND (id_situacion 6)) - Materialize (cost=18.65..23.26 rows=461 width=0) - Nested Loop (cost=0.00..18.19 rows=461 width=0) - Index Scan using active_users on users u (cost=0.00..5.97 rows=1 width=0) Index Cond: (login = 'xxx'::text) Filter: (active AND ((field1 IS NULL) OR (NOT field1))) - Seq Scan on rel_usr_sector_parte_tecnico r (cost=0.00..7.61 rows=461 width=0) (10 rows) Thanks! Fernando. ---(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] Accent insensitive search
Diego Manilla Suárez wrote: Hi. I have a few databases created with UNICODE encoding, and I would like to be able to search with accent insensitivity. There's something in Oracle (NLS_COMP, NLS_SORT) and SQL Server (don't remember) to do this, but I found nothing in PostgreSQL, just the 'to_ascii' function, which AFAIK, doesn't work with UNICODE. to_ascii() doesn't work with UTF-8, but you can use convert() to turn the UTF8 text into Latin-1 and then use to_ascii() to remove the funny bits. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] FUNCTION DEFINITION
ABHANG RANE wrote: Hi, If I have created a plpgsql function, is there a way I can look again at the create statement of that function. I need to know what exactly is there in the function. From psql: \df+ my_function From command-line: pg_dump --schema-only my_database my_db_dump.sql Second one is easier to scroll through/edit. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] FUNCTION DEFINITION
Hi, If I have created a plpgsql function, is there a way I can look again at the create statement of that function. I need to know what exactly is there in the function. Thanks Abhang ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.2 contrib. Full Disjunction
Hi, Let me simplify it in lamer terms. Basically, you have a cycle in your relations schema. i.e. rel A: att-x, att-y rel B: att-y, att-z rel C: att-z, att-x The only way to join these three without loosing a lot of information (aside from some very weird corner cases which i won't mention here), is to use my full disjunctions which is probably most certainly the only implementation of the operation in existence to calculate the general case (which you can see above). Btw, it also applies to more than 3 relations, but be careful, it can be a costly operation. I would try it on very small relations and gradually move up to see how it performs. You can see examples in the contrib module dist in pgfoundry. On Wednesday 06 December 2006 17:37:58 Brandon Aiken wrote: Download the contrib module .tgz from PGFoundry and check out the readme. Syntax is explained therein. It's a multi-step process, it appears. I don't especially care for the term 'full disjunction' to describe this operation, but it seems to be an understood relational operation. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Broersma Jr Sent: Wednesday, December 06, 2006 1:30 AM To: Richard Broersma Jr; General PostgreSQL List Subject: Re: [GENERAL] 8.2 contrib. Full Disjunction 1) what is its application? I guess I found part of my laymans answer: http://www.cs.toronto.edu/~yaron/Presentations/pods2003.ppt#468,39,Examp le 2) how is it used? Maybe this question should have been, what is the syntax? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] FUNCTION DEFINITION
ABHANG RANE wrote: Hi, If I have created a plpgsql function, is there a way I can look again at the create statement of that function. I need to know what exactly is there in the function. select * from pg_proc where proname = 'foo' or \df+ function_name Thanks Abhang ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ORDER BY with exception
On Jun 21, 2007, at 8:08 PM, brian wrote: Michael Glaesemann wrote: On Jun 21, 2007, at 17:35 , brian wrote: I have a lookup table with a bunch of disciplines: To answer your ordering question first: SELECT id, name FROM discipline ORDER BY name = 'other' , name; id |name +- 8 | community 4 | dance 5 | film and television 9 | fine craft 7 | media arts 3 | music 6 | theatre 2 | visual arts 1 | writing 10 | other (10 rows) This relies on the fact that FALSE orders before TRUE. I don't always remember which way, so I often have to rewrite it using or = to get the behavior I want. Of course! (slaps forehead) I don't think you really need to use a function for this. I believe you should be able to do this all in one SQL statement, something like (if I've understood your query and intent correctly): SELECT discipline.name, COUNT(showcase_id) AS total FROM discipline LEFT JOIN ( SELECT DISTINCT discipline_id, showcase.id as showcase_id FROM showcase JOIN showcase_item on (showcase.id = showcase_id) WHERE accepted) AS accepted_showcases ON (discipline.id = discipline_id) GROUP BY discipline.name ORDER BY discipline.name = 'other' , discipline.name; name | total -+--- community | 0 dance | 0 film and television | 0 fine craft | 0 media arts | 0 music | 0 theatre | 0 visual arts | 1 writing | 2 other | 0 (10 rows) That's bang on, Michael, thanks a bunch. I never remember to explore joining on a select. I'm forever thinking in terms of joining on a table. Things to study this evening. One way to break yourself of that habit is to stop thinking in terms of tables when you query and replace that thinking with relations. Queries are made on relations and tables are only one kind of relation. Then just remember that the results of select queries are relations representing relationships between data in other relations so they can themselves be used in select queries (as well as updates, deletes and, as of 8.2, insert and copy statements). Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] SPI using perl
Hi I was wondering if its necessary to download DBI::PgSPI package from CPAN to use spi_exec_query in perl or it can be used without downloading the above mentioned package. I've tried using spi_exec_query without that package but doesn't work. Thanks, Jas
Re: [GENERAL] Can I backup/restore a database in a sql script?
On Jun 22, 2007, at 4:09 AM, Richard Huxton wrote: Joost Kraaijeveld wrote: Hi, I want to write a sql script in which I backup a database and restore a new (altered) version of that database. Is that possible? If so , can anyone give me an example of how to do that? I can run it from any command prompt (psql -U postgres template1 my_db.backup)but I would like it to run from psql (which should give me 1 script for all platforms I must do this: Windows, FreeBSD and Debian) You can restore using just psql (because pg_dump can produce an sql file to be processed). I don't know of any way to backup a database that doesn't use pg_dump. You could script a PITR backup and recovery procedure but I doubt any given single solution would be portable between Windows, Debian, and FreeBSD given the differences in filesystems and filesystem tools although I suppose with a lot of careful work it may be possible. See the chapter on Backup and Restore in the manual for the details of how PITR works (http://www.postgresql.org/docs/8.2/interactive/ backup.html). Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.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] SPI using perl
Jasbinder Singh Bali wrote: Hi I was wondering if its necessary to download DBI::PgSPI package from CPAN to use spi_exec_query in perl No. Using plperl is the way to go. See the docs there are examples under plperl. Joshua D. Drake or it can be used without downloading the above mentioned package. I've tried using spi_exec_query without that package but doesn't work. Thanks, Jas -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] SPI using perl
my $query = SELECT sp_insert( . $a . , . $b . , . $c . ); my $exec_query = spi_exec_query($query); here i'm calling a function sp_insert and passing parameters a,b,c to it. Is this the right usage to spi_exec_query? Thanks, ~Jas On 6/22/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Jasbinder Singh Bali wrote: Hi I was wondering if its necessary to download DBI::PgSPI package from CPAN to use spi_exec_query in perl No. Using plperl is the way to go. See the docs there are examples under plperl. Joshua D. Drake or it can be used without downloading the above mentioned package. I've tried using spi_exec_query without that package but doesn't work. Thanks, Jas -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Re: [GENERAL] Optimizer problem in 8.1.6
Fernando Schapachnik [EMAIL PROTECTED] writes: Now, combined (sorry for the convoluted query, it is build automatically by an app). EXPLAIN SELECT DISTINCT p.id FROM partes_tecnicos p, rel_usr_sector_parte_tecnico r, active_users u WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id AND u.login='xxx' AND r.id_sector=p.id_sector_actual AND p.id_cola_por_ambito=1) OR p.id_cola_por_ambito=1) AND p.id_situacion!=6; Is this query really what you want to do? Because the OR overrides all the join conditions, meaning that rows having p.id_cola_por_ambito=1 AND p.id_situacion!=6 must produce Cartesian products against every row in each of the other tables. I think your SQL-building app is broken. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SPI using perl
Jasbinder Singh Bali wrote: my $query = SELECT sp_insert( . $a . , . $b . , . $c . ); my $exec_query = spi_exec_query($query); here i'm calling a function sp_insert and passing parameters a,b,c to it. Is this the right usage to spi_exec_query? http://www.postgresql.org/docs/8.2/static/plperl-database.html Joshua D. Drake Thanks, ~Jas On 6/22/07, *Joshua D. Drake* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Jasbinder Singh Bali wrote: Hi I was wondering if its necessary to download DBI::PgSPI package from CPAN to use spi_exec_query in perl No. Using plperl is the way to go. See the docs there are examples under plperl. Joshua D. Drake or it can be used without downloading the above mentioned package. I've tried using spi_exec_query without that package but doesn't work. Thanks, Jas -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] How determine a Views dependents
Charles Mortell [EMAIL PROTECTED] writes: I took over an existing PG 8.0 database running on Windows XP. I need to update a view so I know I need to drop the existing view and all the objects that depend on it. The DB is large enough that I can't just look through it with PGAdmin. In an organized way, how can I find out what those dependents are? The traditional way is drop view victim restrict; ... read the error message ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Optimizer problem in 8.1.6
En un mensaje anterior, Tom Lane escribió: Fernando Schapachnik [EMAIL PROTECTED] writes: Now, combined (sorry for the convoluted query, it is build automatically by an app). EXPLAIN SELECT DISTINCT p.id FROM partes_tecnicos p, rel_usr_sector_parte_tecnico r, active_users u WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id AND u.login='xxx' AND r.id_sector=p.id_sector_actual AND p.id_cola_por_ambito=1) OR p.id_cola_por_ambito=1) AND p.id_situacion!=6; Is this query really what you want to do? Because the OR overrides all the join conditions, meaning that rows having p.id_cola_por_ambito=1 AND p.id_situacion!=6 must produce Cartesian products against every row in each of the other tables. I think your SQL-building app is broken. Yes, yes, we found this while working on improving the query. I just wanted to point out that the optimizer was doing a sequential scan in a situation it could unfould de active_users definition, add the login='xxx' clause, and use the index on the users table. Thanks. Fernando. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] writing debug output in perl
Hi, Can anyone help me out with funciton(s) to write a debug output in a perl function on postgres. Thanks, ~Harpreet
Re: [GENERAL] How to install Postgresql on MS Vista?
Joshua D. Drake wrote: Dave Page wrote: Except the administrator account is disabled by default on Vista. Huh? What I am confused about, and I don't pretend in anyway to understand Vista but I just bought my wife a new vista machine and the default user (the one created during setup) was an Administrator. *An* administrator, not *the* administrator. Think of it in terms of root being disabled, with your wife having 'sudo nearly anything' access. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How determine a Views dependents
On Jun 22, 2007, at 10:50 AM, Charles Mortell wrote: Fellow Postgressors, I took over an existing PG 8.0 database running on Windows XP. I need to update a view so I know I need to drop the existing view and all the objects that depend on it. The DB is large enough that I can't just look through it with PGAdmin. In an organized way, how can I find out what those dependents are? If I need to use pg_depend, I could use some help in how to do that. Thanks! Have you looked at the output of \d view name in psql? Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Optimizer problem in 8.1.6
Fernando Schapachnik [EMAIL PROTECTED] writes: En un mensaje anterior, Tom Lane escribió: Is this query really what you want to do? Because the OR overrides all the join conditions, meaning that rows having p.id_cola_por_ambito=1 AND p.id_situacion!=6 must produce Cartesian products against every row in each of the other tables. Yes, yes, we found this while working on improving the query. I just wanted to point out that the optimizer was doing a sequential scan in a situation it could unfould de active_users definition, add the login='xxx' clause, and use the index on the users table. What for, when the query requires fetching the entire table anyway? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Optimizer problem in 8.1.6
Fernando Schapachnik [EMAIL PROTECTED] writes: A rewritten query still exhibits the same behavior: EXPLAIN ANALYZE SELECT DISTINCT p.id FROM partes_tecnicos p WHERE p.id IN (SELECT r.id_parte_tecnico FROM rel_usr_sector_parte_tecnico r, active_users u WHERE (r.id_usr=u.id AND u.login='xxx' AND r.id_sector=p.id_sector_actual AND p.id_cola_por_ambito=1) OR p.id_cola_por_ambito=1) AND p.id_situacion!=6; [ shrug... ] This is still telling the system to perform a Cartesian-product join when p.id_cola_por_ambito=1. A sane formulation of the query might look like EXPLAIN ANALYZE SELECT DISTINCT p.id FROM partes_tecnicos p WHERE (p.id_cola_por_ambito=1 OR p.id IN (SELECT r.id_parte_tecnico FROM rel_usr_sector_parte_tecnico r, active_users u WHERE (r.id_usr=u.id AND u.login='xxx' AND r.id_sector=p.id_sector_actual))) AND p.id_situacion!=6; ie, get the constant term out of the sub-select. This is not exactly the same thing though --- in particular, what do you intend should happen if p.id has no matches whatsoever in r.id_parte_tecnico, yet p.id_cola_por_ambito=1? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Establishing a primary key
I have numerous entries in a column of table 1, some of which are duplicated. I need to transfer this information to table 2 so that I have column that can be used as a primery key. Any help is appreciated. Bob Pawley
[GENERAL] B-tree fan-out
What is the fan-out (number of child nodes) on each B-tree node in postgresql? Is it dependent of the size of the keys being indexed? If so: How? In B-trees all non-leaf nodes have a bunch of pointers to its child nodes. What is the size of such a pointer? Thanks ---(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] Optimizer problem in 8.1.6
On Jun 22, 2007, at 10:16 , Fernando Schapachnik wrote: EXPLAIN SELECT DISTINCT p.id Can you provide EXPLAIN ANALYZE? I suspect that when you rewrote the query it changed how the planner took into account the statistics. If your statistics are off, perhaps this changes how the planner rewrites the query. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Optimizer problem in 8.1.6
On Jun 22, 2007, at 10:16 , Fernando Schapachnik wrote: Maybe this is already solved in more advanced releases, but just in case. VIEW active_users: SELECT * FROM users WHERE active AND ((field IS NULL) OR (NOT field)); You could also rewrite this as SELECT * FROM users WHERE active AND field IS NOT TRUE; IS NOT TRUE returns true for false and null: SELECT true IS NOT TRUE AS true , false IS NOT TRUE AS false , null IS NOT TRUE AS null; true | false | null --+---+-- f| t | t This might be a little easier to read. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] How determine a Views dependents
Fellow Postgressors, I took over an existing PG 8.0 database running on Windows XP. I need to update a view so I know I need to drop the existing view and all the objects that depend on it. The DB is large enough that I can't just look through it with PGAdmin. In an organized way, how can I find out what those dependents are? If I need to use pg_depend, I could use some help in how to do that. Thanks! Charles Mortell Advanced Planning Technologies, Inc. On the Mississippi River at La Crosse, WI cmortell at apt-cafm dot 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
[GENERAL] B-tree fan-out
What is the fan-out (number of child nodes) on each B-tree node in postgresql? Is it dependent of the size of the keys being indexed? If so: How? In B-trees all non-leaf nodes have a bunch of pointers to its child nodes. What is the size of such a pointer? Thanks ---(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] B-tree fan-out
On Jun 22, 2007, at 14:11 , cluster wrote: What is the fan-out (number of child nodes) on each B-tree node in postgresql? Is it dependent of the size of the keys being indexed? If so: How? In B-trees all non-leaf nodes have a bunch of pointers to its child nodes. What is the size of such a pointer? I doubt this is in the documentation, but you can always read the source. I'd take a look around: src/backend/access/nbtree/ starting, with the REAME file. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.2.3 PANIC with corrupted item pointer
On Thu, 21 Jun 2007, Tom Lane wrote: Henka [EMAIL PROTECTED] writes: I happened to notice this error in the log when my application was refused a db connection (quite unexpectedly): PANIC: corrupted item pointer: offset = 3308, size = 28 LOG: autovacuum process (PID 18165) was terminated by signal 6 FWIW, the only occurrences of that specific message text are in PageIndexTupleDelete and PageIndexMultiDelete, so you can be pretty sure that this is just a corrupted-index problem. Once you've identified which table has the problem, a REINDEX should fix it. I've identified the offending index, but REINDEX also causes the PANIC. I also tried DROPping the index (and REINDEX DATABASE foo), but that too causes a panic. Is it possible to identify the physical file/s used by this index and remove it manually from the filesystem (while pg is offline) and update system tables, or is there some other way I can reindex/drop/remove it? The only thing I haven't tried is dropping the entire table, but I suspect that will also cause a panic. If I can't trash the index externally, is possible to trash the entire table/index group externally? Any comments are appreciated. Regards Henry ---(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] 8.2.3 PANIC with corrupted item pointer
Henk - CityWEB [EMAIL PROTECTED] writes: On Thu, 21 Jun 2007, Tom Lane wrote: FWIW, the only occurrences of that specific message text are in PageIndexTupleDelete and PageIndexMultiDelete, so you can be pretty sure that this is just a corrupted-index problem. Once you've identified which table has the problem, a REINDEX should fix it. I've identified the offending index, but REINDEX also causes the PANIC. I also tried DROPping the index (and REINDEX DATABASE foo), but that too causes a panic. In that case you've not correctly identified the broken index. It sounds to me like the problem could possibly be in a system catalog index. REINDEX SYSTEM with use of system indexes disabled might help (see the REINDEX man page). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Establishing a primary key
Bob Pawley wrote: I have numerous entries in a column of table 1, some of which are duplicated. I need to transfer this information to table 2 so that I have column that can be used as a primery key. Any help is appreciated. So, I take it you're wanting to have this so that table 1 stays as it is, and table 2 gets the entries from table 1 made unique, and becomes the parent of table 1? If that's the case, you want something like this: create table2 as select distinct idcolumn from table1; alter table2 add primary key (idcolumn); alter table1 add foreign key (idcolumn) references table2(idcolumn); I think that's about right. ---(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] Establishing a primary key
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Bob Pawley
Sent: 22 juin 2007 14:15
To: Postgresql
Subject: [GENERAL] Establishing a primary key
I have numerous entries in a column of table 1, some of which are
duplicated.
I need to transfer this information to table 2 so that I have column that
can be used as a primery key.
Any help is appreciated.
Bob Pawley
Something like:
Table1{
col1 text,
col2 text
}
Table2{
idtable2 serial,
col1 text,
col2 text,
primary key (idtable2)
}
INSERT INTO Table2 (col1, col2) SELECT col1, col2 FROM Table1;
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Optimizer problem in 8.1.6
En un mensaje anterior, Tom Lane escribió: Fernando Schapachnik [EMAIL PROTECTED] writes: Now, combined (sorry for the convoluted query, it is build automatically by an app). EXPLAIN SELECT DISTINCT p.id FROM partes_tecnicos p, rel_usr_sector_parte_tecnico r, active_users u WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id AND u.login='xxx' AND r.id_sector=p.id_sector_actual AND p.id_cola_por_ambito=1) OR p.id_cola_por_ambito=1) AND p.id_situacion!=6; Is this query really what you want to do? Because the OR overrides all the join conditions, meaning that rows having p.id_cola_por_ambito=1 AND p.id_situacion!=6 must produce Cartesian products against every row in each of the other tables. A rewritten query still exhibits the same behavior: VACUUM verbose ANALYZE users; [...] INFO: analyzing users INFO: users: scanned 778 of 778 pages, containing 22320 live rows and 3 dead rows; 3000 rows in sample, 22320 estimated total rows EXPLAIN ANALYZE SELECT DISTINCT p.id FROM partes_tecnicos p WHERE p.id IN (SELECT r.id_parte_tecnico FROM rel_usr_sector_parte_tecnico r, active_users u WHERE (r.id_usr=u.id AND u.login='xxx' AND r.id_sector=p.id_sector_actual AND p.id_cola_por_ambito=1) OR p.id_cola_por_ambito=1) AND p.id_situacion!=6; Unique (cost=0.00..19045387.60 rows=177 width=4) (actual time=0.331..997.593 rows=209 loops=1) - Index Scan using partes_tecnicos_pkey on partes_tecnicos p (cost=0.00..19045387.16 rows=177 width=4) (actual time=0.323..995.797 rows=209 loops=1) Filter: ((id_situacion 6) AND (subplan)) SubPlan - Result (cost=8.07..90878.33 rows=4493367 width=4) (actual time=0.028..3.250 rows=178 loops=254) One-Time Filter: ($0 = 1) - Nested Loop (cost=8.07..90878.33 rows=4493367 width=4) (actual time=0.025..2.393 rows=216 loops=209) - Seq Scan on users u (cost=0.00..1002.92 rows=9747 width=0) (actual time=0.009..0.009 rows=1 loops=209) Filter: (active AND ((field1 IS NULL) OR (NOT field1))) - Materialize (cost=8.07..12.68 rows=461 width=4) (actual time=0.004..0.800 rows=216 loops=209) - Seq Scan on rel_usr_sector_parte_tecnico r (cost=0.00..7.61 rows=461 width=4) (actual time=0.008..2.128 rows=488 loops=1) Total runtime: 998.552 ms (12 rows) Notice again the seq scan on users instead of using the index and the very off estimate. Thanks. Fernando. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] permission denied for schema
i get this when i try to insert a record into a table. all permissions /
privileges seems ok, but probably i missed something. i am running a
8.1.3 on linux.
registratie= INSERT INTO clienten
(anoniem,geslacht,leeftijd,origine,team_id,hulpverlener_id) VALUES
('true','45','58','864','30','221');
ERROR: permission denied for schema deterp
CONTEXT: SQL statement SELECT 1 FROM ONLY deterp.teams x WHERE
id = $1 FOR SHARE OF x
registratie= \dn
List of schemas
Name| Owner
+---
dekempen | jpe
deterp | jpe
information_schema | postgres
mozaiek| mozaiek_admin
pg_catalog | postgres
pg_toast | postgres
public | postgres
testsaw| testsaw_admin
(8 rows)
registratie= \dn+
List of schemas
Name| Owner |
Access privileges
| Description
+---+--+--
dekempen | jpe | {jpe=UC/jpe,cawdekempen=UC/jpe}
|
deterp | jpe |
{jpe=UC/jpe,deterp_group=UC/jpe,terp_user=UC/jpe}
|
information_schema | postgres |
{postgres=UC/postgres,=U/postgres}
|
mozaiek| mozaiek_admin |
{mozaiek_admin=UC/mozaiek_admin,jpe=UC/mozaiek_admin,mozaiek_user=UC/mozaiek_admin,mozaiek=UC/mozaiek_admin}
|
pg_catalog | postgres |
{postgres=UC/postgres,=U/postgres}
| System catalog schema
pg_toast | postgres |
| Reserved schema for TOAST tables
public | postgres |
{postgres=UC/postgres,=UC/postgres,deterp_group=U/postgres,cawdekempen=U/postgres}
| Standard public schema
testsaw| testsaw_admin |
{testsaw_admin=UC/testsaw_admin,jpe=UC/testsaw_admin,testsaw_user=UC/testsaw_admin,testsaw=UC/testsaw_admin}
|
(8 rows)
registratie= \d clienten
Table deterp.clienten
Column | Type | Modifiers
-+--+---
id | integer | not null default
nextval('clienten_id_seq'::regclass)
anoniem | boolean | not null default false
naam| text |
voornamen | text |
geslacht| smallint |
leeftijd| smallint |
geboortedatum | date |
origine | smallint |
team_id | integer |
hulpverlener_id | integer |
Indexes:
pk_deterp_clienten PRIMARY KEY, btree (id)
Foreign-key constraints:
fk_clienten_geslacht FOREIGN KEY (geslacht) REFERENCES
lijst_items(score) ON UPDATE RESTRICT ON DELETE RESTRICT
fk_clienten_hv_id FOREIGN KEY (hulpverlener_id) REFERENCES
gebruikers(id) ON UPDATE RESTRICT ON DELETE RESTRICT
fk_clienten_leeftijd FOREIGN KEY (leeftijd) REFERENCES
lijst_items(score) ON UPDATE RESTRICT ON DELETE RESTRICT
fk_deterp_clienten_team_id FOREIGN KEY (team_id) REFERENCES
teams(id) ON UPDATE RESTRICT ON DELETE RESTRICT
registratie= \z clienten_id_seq
Access privileges for database registratie
Schema | Name | Type | Access privileges
+-+--+
deterp | clienten_id_seq | sequence |
{jpe=arwdRxt/jpe,deterp_group=arwdRxt/jpe}
(1 row)
registratie= \dg
List of roles
Role name| Superuser | Create role | Create DB | Connections |
Member of
+---+-+---+-+
cawdekempen| no| no | no| no limit|
db_admin | yes | yes | yes | no limit|
dekempen_admin | no| no | no| no limit|
{cawdekempen}
dekempen_user | no| no | no| no limit|
{cawdekempen}
deterp | no| no | no| no limit|
deterp_group | no| no | no| no limit|
deterp_resp| no| no | no| no limit|
jpe| yes | yes | yes | no limit|
{mozaiek}
liesbet| no| no | no| no limit|
{cawdekempen}
mozaiek| no| no | no| no limit|
mozaiek_admin | no| yes | yes | no limit|
{mozaiek}
mozaiek_user | no| no | no| no limit|
{mozaiek}
postgres | yes | yes | yes | no limit|
terp_admin | no| no | no| no limit|
{deterp_group}
terp_user | no|
[GENERAL] Possible bug (or I don't understand how foreign keys should work with partitions)
People,
either I don't understand how partitions works or I think I found a bug
here.
I'm using PostgreSQL-8.2.4 with Gentoo.
The code explains:
# First I create the table regs with 2 partitions:
create table regs (rID serial primary key, name text, number int);
create table regs_00 ( CHECK ( number = 00 AND number 10 )) INHERITS
(regs);
create rule insert_regs_00 AS ON INSERT TO regs WHERE ( number = 00 AND
number 10 ) DO INSTEAD INSERT INTO regs_00 VALUES ( NEW.rID, NEW.name,
NEW.number );
create table regs_10 ( CHECK ( number = 10 AND number 20 )) INHERITS
(regs);
create rule insert_regs_10 AS ON INSERT TO regs WHERE ( number = 10 AND
number 20 ) DO INSTEAD INSERT INTO regs_10 VALUES ( NEW.rID, NEW.name,
NEW.number );
# Them I create the table regsemail also with 2 partitions but with a
foreign key:
create table regsemail (dID serial primary key, fk_regs_id integer
REFERENCES regs (rID) ON DELETE CASCADE, email text);
create table regsemail_00 ( CHECK ( fk_regs_id = 0 AND fk_regs_id 10 ))
INHERITS (regsemail);
CREATE RULE insert_regsemail_00 AS ON INSERT TO regsemail WHERE ( fk_regs_id
= 0 AND fk_regs_id 10 ) DO INSTEAD INSERT INTO regsemail_00 VALUES (
NEW.dID, NEW.fk_regs_id, NEW.email );
create table regsemail_10 ( CHECK ( fk_regs_id = 10 AND fk_regs_id 20 ))
INHERITS (regsemail);
CREATE RULE insert_regsemail_10 AS ON INSERT TO regsemail WHERE ( fk_regs_id
= 10 AND fk_regs_id 20 ) DO INSTEAD INSERT INTO regsemail_10 VALUES (
NEW.dID, NEW.fk_regs_id, NEW.email );
# Insert four rows in regs (rID will go from 1 to 4):
insert into regs (name, number) values ('Daniel', 4);
insert into regs (name, number) values ('Daniel', 14);
insert into regs (name, number) values ('Daniel', 5);
insert into regs (name, number) values ('Daniel', 15);
# Insert a 'invalid' row in regsemail
insert into regsemail (fk_regs_id, email) values (6, '[EMAIL PROTECTED]');
# END!
I should get an error saying something like ...violates foreign key
constraint... but I'm not getting anything. That's the bug. If I don't have
the partitions them I get the error message (as I think I should).
The problem I'm trying to solve is: I'll have a 1.8 billion rows table
(regs) and another one having at least one row to each row from the first
one. The solution is very simple: partitions. The 1.8 billion rows is
distributed uniformly in the days of the year, so I'll create one partition
for each day. But I have to do something similar with the second table as
well otherwise I wouldn't win much if I had to do a JOIN. I was testing how
foreign keys would work in this case and ran into this.
Is this really a bug? If not, what am I doing wrong please?
Best regards,
Daniel
[GENERAL] creating temporary table PostgreSql 8.1 and iBatis 2.3
Greetings! I've build a script in pgAdmin 3 ( os x 10.4 on ppc) that does a select, some left outer joins and puts the results into a temporary table. Works great when I run in pgAdmin. Then I try to use in an iBatis sqlmap as a 'select' and get this when trying to execute it: org.postgresql.util.PSQLException: ERROR: must be member of role postgres Other than that queries run fine. I'm thinking the problem is on the db admin side of things, but I'm at a loss as to how to start fixing it. I'll greatly appreciate any feedback. Thank you in advance.
Re: [GENERAL] [JDBC] creating temporary table PostgreSql 8.1 and iBatis 2.3
On Fri, 22 Jun 2007, tomasz brymora wrote: I've build a script in pgAdmin 3 ( os x 10.4 on ppc) that does a select, some left outer joins and puts the results into a temporary table. Works great when I run in pgAdmin. Then I try to use in an iBatis sqlmap as a 'select' and get this when trying to execute it: org.postgresql.util.PSQLException: ERROR: must be member of role postgres Other than that queries run fine. I'm thinking the problem is on the db admin side of things, but I'm at a loss as to how to start fixing it. Knowing the SQL that it actually executed would be valuable. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Possible bug (or I don't understand how foreign keys should work with partitions)
I was testing how foreign keys would work in this case and ran into this. Is this really a bug? If not, what am I doing wrong please? Here is what the postgresql manual says about this: A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. Thus, in the terms of the above example: http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html#DDL-INHERIT-CAVEATS Overcoming this limitation is on the todo list, but as far as I know it isn't slated to be fixed in the upcomming 8.3. Perhaps 8.4 will include this feature. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] permission denied for schema
jef peeraer [EMAIL PROTECTED] writes:
i get this when i try to insert a record into a table. all permissions /
privileges seems ok, but probably i missed something. i am running a
8.1.3 on linux.
registratie= INSERT INTO clienten
(anoniem,geslacht,leeftijd,origine,team_id,hulpverlener_id) VALUES
('true','45','58','864','30','221');
ERROR: permission denied for schema deterp
CONTEXT: SQL statement SELECT 1 FROM ONLY deterp.teams x WHERE
id = $1 FOR SHARE OF x
That's a foreign-key check, which is supposed to be done as the owner of
the table. You did not show us who owns table clienten, but I think
that role must be missing the intended(?) membership in deterp_group.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [JDBC] creating temporary table PostgreSql 8.1 and iBatis 2.3
Kris Jurka [EMAIL PROTECTED] writes: On Fri, 22 Jun 2007, tomasz brymora wrote: I've build a script in pgAdmin 3 ( os x 10.4 on ppc) that does a select, some left outer joins and puts the results into a temporary table. Works great when I run in pgAdmin. Then I try to use in an iBatis sqlmap as a 'select' and get this when trying to execute it: org.postgresql.util.PSQLException: ERROR: must be member of role postgres Knowing the SQL that it actually executed would be valuable. Indeed. A quick scan of the backend source says that that message is only issued if you try to give away ownership of something to a role you are not a member of (this means ALTER OWNER and the options of CREATE DATABASE/CREATE SCHEMA that give the object some other owner than yourself). So there's something you didn't tell us about what that script does. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] permission denied for schema
Tom Lane schreef:
jef peeraer [EMAIL PROTECTED] writes:
i get this when i try to insert a record into a table. all permissions /
privileges seems ok, but probably i missed something. i am running a
8.1.3 on linux.
registratie= INSERT INTO clienten
(anoniem,geslacht,leeftijd,origine,team_id,hulpverlener_id) VALUES
('true','45','58','864','30','221');
ERROR: permission denied for schema deterp
CONTEXT: SQL statement SELECT 1 FROM ONLY deterp.teams x WHERE
id = $1 FOR SHARE OF x
That's a foreign-key check, which is supposed to be done as the owner of
the table. You did not show us who owns table clienten, but I think
that role must be missing the intended(?) membership in deterp_group.
registratie=# \z deterp.clienten;
Access privileges for database registratie
Schema | Name | Type |Access privileges
+--+---+--
deterp | clienten | table | {jpe=arwdxt/jpe,deterp_group=arwdxt/jpe}
(1 row)
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] pg_restore out of memory
Alvaro Herrera writes: The problem is probably the ulimit. The problem occurrs on i386, but not on 64bit architecture. Tested 5 machines. 3 i386 FreeBSD 6.2 2 AMD64 FreeBSD 6.2 The 64 bit machines, with postgresql compiled from ports, worked. One of the machines had default OS limit. The second is already a dedicated postgresql machine so it already had OS limits increased. So is this a bug in the i386 version of Postgresql or a limitation of the FreeBSD i386? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] AutoVacuum Behaviour Question
Bruce McAlister wrote: Hi All, I have enabled autovacuum in our PostgreSQL cluster of databases. What I have noticed is that the autovacuum process keeps selecting the same database to perform autovacuums on and does not select any of the others within the cluster. Is this normal behaviour or do I need to do something more elaborate with my settings? There are two reasons autovacuum would keep picking up the same database: 1. the other databases do not have pgstat entries. 2. this database is in danger of Xid wraparound and the vacuum run fails to complete for some reason. Our main concern is the blueface-service database. The sipaccounts table has some high traffic, mainly updates. Are there non-null values in the pg_stat views for tables in blueface-service database? If there are, then you can discard (1) as the problem. If all values are nulls for all tables, then you have the stats collector disabled for that database, or something (maybe by ALTER DATABASE ... SET). In this case, reenable it and issue a manual VACUUM so that pgstat is populated. (I think the easiest way to check is SELECT datname, datconfig FROM pg_database). Regarding (2) you would need to check whether the autovacuum run dies with an ERROR. I'd advise setting a log_line_prefix that included the PID (%p) so that you can check whether the process goes away cleanly or it dies early. It is not impossible that a corrupt index or table is causing autovacuum to die, but it should certainly show up in the logs. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 El que vive para el futuro es un iluso, y el que vive para el pasado, un imbécil (Luis Adler, Los tripulantes de la noche) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Proposed Feature
Bruce Momjian wrote: Naz Gassiep wrote: I'm using PG on windows for the first time (as of about 6 minutes ago). I was thinking that it would be great to have a system tray icon with a running indicator, kind of like the way Apache2.x for windows has, or even MSSQL. Perhaps the PG logo with a small white circle with a red square or a green triangle in the same fashion. Just a thought. And what does the icon show or do? That would be pretty easy to do with Delphi. I could whip something up and donate it to the project with a BSD license. It could be a green Play arrow if the service is running and a red one if it is not, then have a few right click options to start/stop/restart the service. I was thinking the same thing awhile back, but forgot about it. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Dynamic Log tigger (plpgsql)
On Jun 16, 2007, at 6:26 AM, Noah Heusser wrote:
I want to implement a trigger-function witch can fill the following
table.
Each data manipulation (INSERT, UPDATE or DELETE) gets logged.
The function should work as trigger on diffrent tables.
CREATE TABLE logtable (
operationCHAR(6) CHECK (change_type IN ('DELETE',
'INSERT', 'UPDATE')),
Note that that field will take 12 bytes in 8.2, and assuming that
varvarlena is in 8.3, 8 bytes there (or is varvarlena byte-aligned?)
You might be better going with char (with the double-quotes) and
'D', 'I', and 'U'.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(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] A problem in inheritance
On Jun 20, 2007, at 12:53 PM, Talha Khan wrote: THE ACTUAL TEST: DROP RULE account_login_no_update ON account_login; UPDATE account_login set originating_source = 'xxx'; Now the update should not effect the child table but it does, evident from the output of the following query: That's because you dropped the rule that would have affected that query. Remember that rules effectively work on the query strings themselves, so to impact that update you'd have to have a rule on account_login. No rule on a child table will matter. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] permission denied for schema
jef peeraer [EMAIL PROTECTED] writes:
Tom Lane schreef:
That's a foreign-key check, which is supposed to be done as the owner of
the table. You did not show us who owns table clienten, but I think
that role must be missing the intended(?) membership in deterp_group.
registratie=# \z deterp.clienten;
Access privileges for database registratie
Schema | Name | Type |Access privileges
+--+---+--
deterp | clienten | table | {jpe=arwdxt/jpe,deterp_group=arwdxt/jpe}
Hmm. This doesn't actually say so, but I think that jpe must be the
owner of clienten, and he's also the owner of the schema. Which makes
the failure really darn odd.
I tried to reproduce the situation on 8.1.9 and couldn't see any
problem. I do not see anything in the 8.1 CVS history that looks
related, but it's slightly possible this is a bug that's fixed between
8.1.3 and 8.1.9 (and even if that is not the case, you really really
ought to update anyway).
Would you see if the problem recurs if you do a pg_dump -s and load the
schema into a fresh database? If it doesn't, perhaps there is some
weird corruption in the ACL entries. If it is reproducible, please post
the complete schema (or enough of it to reproduce the problem) to
pgsql-bugs.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Proposed Feature
Hi, It provides a very quick and easy status check (running or not) as well as right click access to start/stop and any other basic admin features that may be appropriate. I'd be happy with seeing whether it was running, as well as start/stop accessible via one right click. It's the same as Apache for Win32, MSSQL any many other server programs. - Naz. Bruce Momjian wrote: Naz Gassiep wrote: I'm using PG on windows for the first time (as of about 6 minutes ago). I was thinking that it would be great to have a system tray icon with a running indicator, kind of like the way Apache2.x for windows has, or even MSSQL. Perhaps the PG logo with a small white circle with a red square or a green triangle in the same fashion. Just a thought. And what does the icon show or do?
Re: [GENERAL] Proposed Feature
Hey, I'm sure that'd be greatly appreciated, most other major servers and DBs have a similar feature, and that's what the systray is for, i.e., viewing major user-installed services. - Naz. Tony Caduto wrote: Bruce Momjian wrote: Naz Gassiep wrote: I'm using PG on windows for the first time (as of about 6 minutes ago). I was thinking that it would be great to have a system tray icon with a running indicator, kind of like the way Apache2.x for windows has, or even MSSQL. Perhaps the PG logo with a small white circle with a red square or a green triangle in the same fashion. Just a thought. And what does the icon show or do? That would be pretty easy to do with Delphi. I could whip something up and donate it to the project with a BSD license. It could be a green Play arrow if the service is running and a red one if it is not, then have a few right click options to start/stop/restart the service. I was thinking the same thing awhile back, but forgot about it. Later, ---(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] regexp searching in arrays not working?
On Tue, Jun 19, 2007 at 07:36:02PM -0400, Tom Lane wrote: Rhys Stewart [EMAIL PROTECTED] writes: Is regex searching not functioning (as i expect it to?) ~ expects the pattern on the right, not the left. So it's taking your array entries as patterns, which don't match the data 'Trans'. Since there's no (array) ANY op scalar syntax, Why isn't there? Is it forbidden by the SQL standard? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] regexp searching in arrays not working?
David Fetter [EMAIL PROTECTED] writes: On Tue, Jun 19, 2007 at 07:36:02PM -0400, Tom Lane wrote: Since there's no (array) ANY op scalar syntax, Why isn't there? Is it forbidden by the SQL standard? Well, the spec has quantified comparison predicate ::= row value expression comp op quantifier table subquery quantifier ::= all | some all ::= ALL some ::= SOME | ANY and not the reverse. The array syntax is an abuse of this notation and doesn't have the reverse either. I'm not really sure how much new code would be needed to support the other case, or whether it would be worth the trouble. 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
