[BUGS] BUG #6428: pg_restore -l not consistent with function comments
The following bug has been logged on the website: Bug reference: 6428 Logged by: Keith Email address: ke...@omniti.com PostgreSQL version: 9.1.2 Operating system: Any Description: Working on a tool that iterates through the pg_restore -l list for doing more advanced dump filtering (pg_extractor), I came across an inconsistency in the object list that is generated. If a function is declared with the argument variable names defined in the argument list, and has an associated comment, the line for the comment in the pg_restore -l list includes the variable name in the argument list whereas the line for the function itself just has the argument types. This has made it hard for me to match comments with functions. It just seems very inconsistent as well since the function signature does not require the argument variable names to uniquely identify it. Not a hugely critical item, but it's made it hard for me to make my filtering tool as useful as I'd like it to be. Example: 14663; 1255 16507 FUNCTION keith do_something(integer, text) postgres 48818; 0 0 COMMENT keith FUNCTION do_something(data_source_id integer, query text) postgres -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6581: pg_dumpall --no-tablespaces option still sets default tablespace
The following bug has been logged on the website: Bug reference: 6581 Logged by: Keith Fiske Email address: ke...@omniti.com PostgreSQL version: 9.1.3 Operating system: Any Description: Using the --no-tablespaces option for pg_dumpall correctly stops tablespace creation and each individual object from having its tablespace set. But if the original database had its default tablespace changed, this is still added at the beginning of the dump file: ALTER DATABASE dbname SET default_tablespace TO 'old_db_tablespace'; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #7716: Only owner can drop trigger?
The following bug has been logged on the website: Bug reference: 7716 Logged by: Keith Fiske Email address: ke...@omniti.com PostgreSQL version: 9.2.1 Operating system: Any Description: This is documented in the DROP TRIGGER section of the documentation, but it doesn't seem to make any sense. If a role has permission to create a trigger, why can that same role not have the same permissions to drop it, even if that role doesn't own the table? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #7923: PGP secret key with password decryption not working
The following bug has been logged on the website:
Bug reference: 7923
Logged by: Keith Fiske
Email address: ke...@omniti.com
PostgreSQL version: 9.2.3
Operating system: Debian/Ubuntu/Solaris
Description:
Running into an issue when we tried to add a password to a gpg secret key
used to encrypt data in the database. Things have been working fine with no
password on they key, but that isn't ideal. I was able to recreate this
issue on my local install of postgres, so it's not just an issue on the
server where I first noticed it. Below is the process I went through,
generating a key, moving it to where postgres can read it, encrypting and
then attempting to decrypt the data. I know the password I gave is correct
because I used the same password to remove it from the key and then things
just worked.
Ran into someone else reporting pretty much the same issue but no resolution
other than removing the password.
http://bytes.com/topic/postgresql/answers/943536-corrupt-data-error-decryption-postgresql-using-gnupg
gpg --gen-key
gpg (GnuPG) 1.4.11; Copyright (C) 2010 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Please select what kind of key you want:
(1) RSA and RSA (default)
(2) DSA and Elgamal
(3) DSA (sign only)
(4) RSA (sign only)
Your selection? 1
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048)
Requested keysize is 2048 bits
Please specify how long the key should be valid.
0 = key does not expire
= key expires in n days
w = key expires in n weeks
m = key expires in n months
y = key expires in n years
Key is valid for? (0)
Key does not expire at all
Is this correct? (y/N) y
You need a user ID to identify your key; the software constructs the user
ID
from the Real Name, Comment and Email Address in this form:
"Heinrich Heine (Der Dichter) "
Real name: Test Pgcrypto
Email address: t...@pgcrypto.com
Comment:
You selected this USER-ID:
"Test Pgcrypto "
Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
...+
+
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
Not enough random bytes available. Please do some other work to give
the OS a chance to collect more entropy! (Need 8 more bytes)
..+
..+
gpg: key 734ECABE marked as ultimately trusted
public and secret key created and signed.
gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0 valid: 1 signed: 0 trust: 0-, 0q, 0n, 0m, 0f, 1u
pub 2048R/734ECABE 2013-03-06
Key fingerprint = C25C 7B89 B2B4 674F 551A 2E5F FA95 0DAF 734E CABE
uid Test Pgcrypto
sub 2048R/F122A512 2013-03-06
Exported public & secrect keys in binary form and copied to
$datadir/keys/
keith=# select
pgcrypto.pgp_key_id(pg_read_binary_file('keys/test_pgcrypto_public.key'));
pgp_key_id
------
3E265C23F122A512
(1 row)
keith=# select
pgcrypto.pgp_key_id(pg_read_binary_file('keys/test_pgcrypto_secret.key'));
pgp_key_id
--
3E265C23F122A512
(1 row)
keith=# create table test_pgcrypto(data_plain text, data bytea);
CREATE TABLE
keith=# insert into test_pgcrypto (data_plain, data) values ('1234567890',
pgcrypto.pgp_pub_encrypt('1234567890',
pg_read_binary_file('keys/test_pgcrypto_public.key')));
INSERT 0 1
keith=# select pgcrypto.pgp_pub_decrypt(data,
pg_read_binary_file('keys/test_pgcrypto_secret.key')) from test_pgcrypto ;
ERROR: Need password for secret key
keith=# select pgcrypto.pgp_pub_decrypt(data,
pg_read_binary_file('keys/test_pgcrypto_secret.key'), 'stuff') from
test_pgcrypto ;
ERROR: Corrupt data
$ gpg --list-secret-keys
--
sec 2048R/734ECABE 2013-03-06
uid Test Pgcrypto
ssb 2048R/F122A512 2013-03-06
$ gpg --edit-key F122A512
gpg (GnuPG) 1.4.11; Copyright (C) 2010 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Secret key is available.
pub 2048R/734ECABE created: 2013-03-06 expires: never usage: SC
trust: ultimate validity: ultimate
sub 2048R/F122A512 created: 2013-03-06 expires: never usa
[BUGS] BUG #4854: Problems with replaying WAL files on Warm Standby
The following bug has been logged online: Bug reference: 4854 Logged by: Keith Pierno Email address: kpie...@lulu.com PostgreSQL version: 8.3.6 Operating system: Red Hat Enterprise Linux AS release 4 (Nahant Update 6) PPC64 Description:Problems with replaying WAL files on Warm Standby Details: We experienced a failure of our primary database server, after fixing the hardware issue I attempted to turn the old primary into a warm standby slave. I used a hotbackup from the new primary server and restored the files to the slave. I then removed all references to old WAL files and used rsync to copy the appropriate WAL files from our archive location. During the log playback I received the following errors. 2009-06-15 13:25:11 EDT <::> LOG: restored log file "00040746007F" from archive 2009-06-15 13:25:11 EDT <::> LOG: restored log file "000407460080" from archive 2009-06-15 13:25:11 EDT <::> LOG: restored log file "000407460081" from archive 2009-06-15 13:25:11 EDT <::> LOG: restored log file "000407460082" from archive 2009-06-15 13:25:12 EDT <::> LOG: restored log file "000407460083" from archive 2009-06-15 13:25:12 EDT <::> LOG: restored log file "000407460084" from archive 2009-06-15 13:25:12 EDT <::> PANIC: unexpected timeline ID 6 (after 4) in checkpoint record 2009-06-15 13:25:12 EDT <::> CONTEXT: xlog redo checkpoint: redo 746/84FFF010; tli 6; xid 0/50577196; oid 31395572; multi 6542; offset 13124; shutdown 2009-06-15 13:25:13 EDT <::> LOG: startup process (PID 27836) was terminated by signal 6: Aborted 2009-06-15 13:25:13 EDT <::> LOG: aborting startup due to startup process failure This backup was created at 0205 EDT and the WAL in question was from 0743 EDT. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4854: Problems with replaying WAL files on Warm Standby
Thanks for the prompt response. If such a file is needed for recovery it was never created by postgres. The current archiving process creates uses rsync to archive the WAL files to a shared archive area. In the past and on my other cluster we do not see .history files on the primary server and have been able to recover without them. If it helps I can send a copy of the WAL files and my postgres.conf files. Keith Tom Lane wrote: "Keith Pierno" writes: 2009-06-15 13:25:12 EDT <::> PANIC: unexpected timeline ID 6 (after 4) in checkpoint record Hmm. It's complaining because it didn't find timeline 6 mentioned in the timeline history file it read (if any). Maybe you forgot to archive or restore the .history file? regards, tom lane
Re: [BUGS] BUG #4854: Problems with replaying WAL files on Warm Standby
I was able to find the file, which was from the date of the failure (Tuesday June 9th). My question is why the backup from this morning would all of a sudden require a file from the 9th even though 5 hours of logs were able to be applied? I ended up doing another hotbackup and restoring the database from this afternoon and this seems to be working fine. I am just worried that another WAL file will throw the same error and I will be without a warm standby. Thanks again, Keith Tom Lane wrote: Keith Pierno writes: Thanks for the prompt response. If such a file is needed for recovery it was never created by postgres. The current archiving process creates uses rsync to archive the WAL files to a shared archive area. In the past and on my other cluster we do not see .history files on the primary server and have been able to recover without them. The .history file would have been created on the slave at the time it became new master (and started its own timeline). regards, tom lane
Re: [BUGS] BUG #4854: Problems with replaying WAL files on Warm Standby
The backup used was from well after the failover time which is why I was concerned. Interestingly enough the logs are still all prefixed with 0004... That just makes this problem extremely bizarre. Current log listing: [postg...@db01pri pgwalarchives]# ls -ltr total 82028 -rw--- 1 postgres postgres 250 Jun 15 02:05 0004074500DB.00F290D8.backup -rw--- 1 postgres postgres 250 Jun 15 13:28 000407470065.00D48908.backup -rw--- 1 postgres postgres 16777216 Jun 15 15:58 0004074700E4 -rw--- 1 postgres postgres 16777216 Jun 15 15:59 0004074700E5 -rw--- 1 postgres postgres 16777216 Jun 15 16:02 0004074700E6 -rw--- 1 postgres postgres 16777216 Jun 15 16:04 0004074700E7 -rw--- 1 postgres postgres 16777216 Jun 15 16:07 0004074700E8 [postg...@db01pri pgwalarchives]# Keith Tom Lane wrote: Keith Pierno writes: I was able to find the file, which was from the date of the failure (Tuesday June 9th). My question is why the backup from this morning would all of a sudden require a file from the 9th even though 5 hours of logs were able to be applied? It was apparently busy applying the logs that were from the old primary. When it got up to the point where the failover occurred, kaboom :-(. It would have had trouble in any case there because the subsequent log files are presumably numbered 0006... instead of 0004... Were you getting those shipped over properly from the new primary? regards, tom lane
Re: [BUGS] BUG #4854: Problems with replaying WAL files on Warm Standby
The timeline for the events all dates MM/DD/ 06/09/2009 1310 EDT - Hardware fault on primary database server db01pri 06/09/2009 1325 EDT - Failover to warm standby db01sec 06/12/2009 1615 EDT - db01pri server fixed and OS booted 06/15/2009 1115 EDT - started recovery of hotbackup from 06/15/2009 0205 EDT from db01sec onto db01pri 06/15/2009 1320 EDT - Attempted to start postgres on db01pri in warm standby mode 06/15/2009 1325 EDT - Failure to apply WAL log errors with "unexpected timeline ID" 06/15/2009 1340 EDT - Started a new hotbackup on db01sec 06/15/2009 1545 EDT - Started recovery hotbackup from 06/15/2009 1340 to db01pri 06/15/2000 1430 EDT - db01pri recovered and running in warm standby Here is the contents of the pg_xlog directory and the 0004.history file: [postg...@db01pri ~]$ cat 0004.history 1 0001073600A1 before transaction 0 at 1999-12-31 19:00:00-05 [postg...@db01pri ~]$ ls -l total 98468 -rw--- 1 postgres postgres 74 Jul 10 2008 0002.history -rw--- 1 postgres postgres 74 Jun 9 13:29 0003.history -rw--- 1 postgres postgres 16777216 Jun 16 08:45 0004074900C9 -rw--- 1 postgres postgres 16777216 Jun 16 08:46 0004074900CA -rw--- 1 postgres postgres 16777216 Jun 16 08:47 0004074900CB -rw--- 1 postgres postgres 74 Jun 9 13:33 0004.history drwxr-xr-x 2 postgres postgres 32768 Jun 16 08:46 archive_status -rw--- 1 postgres postgres 16777216 Jun 9 13:45 xlogtemp.17243 -rw--- 1 postgres postgres 16777216 Jun 9 13:45 xlogtemp.17244 -rw--- 1 postgres postgres 16777216 Jun 9 13:52 xlogtemp.17397 [postg...@db01pri ~]$ Thanks again, Keith Tom Lane wrote: Keith Pierno writes: The backup used was from well after the failover time which is why I was concerned. Interestingly enough the logs are still all prefixed with 0004... That just makes this problem extremely bizarre. Hmm, that *is* weird. It seems like the new primary must have reverted its decision to go from timeline 4 to timeline 6. (Which in itself is a bit odd; why not timeline 5?) Can you give us an exact sequence of events on the slave server/new primary around the time of the failover? Also, what was in the .history file when you found it, and are there any other history files? regards, tom lane
[BUGS] BUG #5032: unexpected syntax error for plpgsql function returns table
The following bug has been logged online: Bug reference: 5032 Logged by: Keith Cascio Email address: ke...@cs.ucla.edu PostgreSQL version: 8.4.0 Operating system: CentOS 5.3 (Linux) Description:unexpected syntax error for plpgsql function returns table Details: Do this: create function reproduce() returns table(foo integer) language plpgsql as 'begin return query select 1 foo; end;'; Output I got: ERROR: syntax error at or near "$1" LINE 1: select 1 $1 ^ QUERY: select 1 $1 CONTEXT: SQL statement in PL/PgSQL function "reproduce" near line 1 Output I expected: CREATE FUNCTION The mechanism of this error involves the "foo" return column and the "foo" alias being identical. Identifier collision? If I change one of the "foo"s to "bar", the definition succeeds. There are fancier ways to cause similar unexpected syntax errors, but this is close to a minimal example. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5032: unexpected syntax error for plpgsql function returns table
Pavel, On Thu, 3 Sep 2009, Pavel Stehule wrote: > it's not bug - PostgreSQL doesn't support parameter placeholder on this > position. Use dynamic query instead - plpgsql statement EXECUTE. Thank you for your reply. I appreciate your suggestion, but it still seems like a bug to me. Please comment on the fact that the following code succeeds: create function reproduce() returns table(foo integer) language plpgsql as 'begin return query select 1 bar; end;'; Output in psql is: CREATE FUNCTION Why should it succeed with "bar" but not with "foo"? Thanks, Keith > 2009/9/3 Keith Cascio : > > create function reproduce() returns table(foo integer) language plpgsql as > > 'begin return query select 1 foo; end;'; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5032: unexpected syntax error for plpgsql function returns table
Pavel, On Thu, 3 Sep 2009, Pavel Stehule wrote: > 2009/9/3 Keith Cascio : > > Why should it succeed with "bar" but not with "foo"? > > because bar isn't declared as variable I understand now. returns table(v1 t1, v2 t2,...) is equivalent to declaring OUT parameters, therefore "foo" is an out parameter. Thank you for your patience sir. Keith -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Spelling error in 8.1.6-1 Windows Error Dialog typo
Not sure where to find the TODO list per the bug reporting guidelines, but this is pretty simple. It may be fixed in newer versions, but we are using this version until 8.2 is tested on our systems. Do with this what you will. When you create a superuser with a semi-colon or quotes the error dialog starts "Passwords may not conain quotes..." missing the "t" in contain. Keith
[BUGS] BUG #3644: website no showing playlist for radio
The following bug has been logged online: Bug reference: 3644 Logged by: keith atkinson Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Operating system: windows xp pro Description:website no showing playlist for radio Details: i recently updated my database as i run a radio using sam broadcaster and a website for request but since i updated it the playlist Z does not show up if i do a search using the website the list of Z come up but it only happened after i updated could you please help me out if you need to look at whats going on goto http://www.lankys.co.uk/request/playlist.php as you will see the rest do thanks in advance ---(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
[BUGS] BUG #4041: error in Application Stack Builder
The following bug has been logged online: Bug reference: 4041 Logged by: keith chan Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3 Operating system: Windows XP with SP2 Description:error in Application Stack Builder Details: I did followings 1. click Start at bottom-left of Windows XP and select Programs 2. select PostgreSQL 8.3 and Application Stack Builder 3. In Stack Builder dialog form, select PosgreSQL Database Server 8.3 on port 5432 in dropdown list 4. click Next button. It shows a error dialog form Stack Builder Error with message "Failed to open the application list: http://www.postgresql.org/applications.xml Error: The URL specified could not be opened." -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] currval and nextval in 7.3.4
Hi,
I recently installed 7.3.4 (complete install from scratch) and both 'select
nextval('my_seq') from my_table' and 'select currval('my_seq') from my_table'
return a number of rows equal to the number of rows in the table.
The sequence was created with a SERIAL type if that helps.
In 'psql' the results look like this.
my_db=# select nextval('my_seq') from my_table;
nextval
-
6
7
8
9
(4 rows)
my_db=# select currval('my_seq') from my_table;
currval
-
9
9
9
9
(4 rows)
I get the same results using the JDBC driver so it's not a psql problem.
Any thoughts out there?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1366: spam from yourselves
The following bug has been logged online: Bug reference: 1366 Logged by: keith berrington Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Operating system: not applicable Description:spam from yourselves Details: I have been trying unsuccessfully for over 2 months to remove myself from the mailing list. Despite attempting to unsubscibe, sending spam reports to spamcop and notifying the authorities you take no notice of me. What do i need to do to unsubscibe. Please do not tell me to email "unregister YourEmailAddressHere" to [EMAIL PROTECTED])as it does not work. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] BUG #1552: massive performance hit between 7.4 and 8.0.1
They PGSQL-PERFORMANCE list is really the appropriate place for performance issues like yours. Subscribe? Josh, Brian and I are trying to put upwards of 80-90,000 rows into a table. When we run on PostgreSQL 7.4, this takes about five minutes. On comparable hardware, running PostgreSQL 8.0.1, it's taking almost two hours. The query from the bug report shows that the planner is using sequential searches, not index searches, to satisfy the foreign key constraint. Our scenario (inserting data into a new table) seems to be something that a lot of users could run across. The performance drop we're seeing shows up as a regression from earlier behaviour in PostgreSQL. That's why, after discussing our issue with AndrewSN on #postgresql, Brian filed a bug here. We'll follow up with the PERFORMANCE list to find a workaround, but I think this is an issue which should be addressed as a bug. Thanks for your help, Keith Browne [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1552: massive performance hit between 7.4 and 8.0.1
Simon Riggs wrote: The EXPLAINs you've enclosed are for SELECTs, yet your bug report describes INSERTs as being the things that are slow. [You may find better performance from using COPY] Simon, Brian and I are working together on this problem. We're starting with an empty database, creating four tables, and populating those tables with a total of 180,000-200,000 rows. Each table has a primary key, and several of the tables reference foreign keys in other tables. We've written a Python script, using psycopg, which executes all the queries to create the tables and insert the rows. The database is running on the same machine where the script runs. I've seen similar performance when issuing a COMMIT after each insertion, and also after batching insertions in blocks of 250 per COMMIT, so batching the commits is not helping much. I've looked at the possibility of using COPY, but in our production environment it will be prohibitive to build a flat file with all this data. I'd rather generate it on the fly, as we've been able to do with PostgreSQL 7.4. Also, your tests have compared two systems, so it might be that the hardware or configuration of one system is different from the other. When running with PostgreSQL 7.4 on a dual-CPU Athlon MP2400+ machine with a gigabyte of RAM, running Debian Linux version 2.6.8.1, we were able to insert all this data in 5-7 minutes. It's taken a while to install Postgres 8.0.1 on the same machine, but now I have, and it's taking 40-45 minutes to run the same insert script. This is similar to the performance we saw on another machine, a fast single-CPU AMD64 box running Gentoo. I don't think it's a hardware issue. I dug around a bit, and found suggestions that this sort of problem could be worked around by breaking the database connection and restarting it after the tables had been partially filled. I modified our script to break and re-establish the database connection when each table first has 4,000 records inserted, and the performance is greatly improved; it now takes only about 3.5 minutes to insert 180,000+ rows. I've since modified this script to build and populate a fifth table with over 1.3 million rows. The fifth table has no primary key, but lists a foreign key into one of the first four tables. With the above modification (break and re-build the DB connection after 4,000 rows have been inserted), the whole database can be populated in about 15 minutes. I wouldn't have dared try to build a one-million-plus-row table until I found this speed-up. If you could repeat the test on one single system, then this would assist in the diagnosis of this bug report. Also, if you could describe the workload that is giving you a problem more exactly, that would help. Specifically, can you confirm that you have run ANALYZE on the tables, and also give us some idea of numbers of rows in each table at the time you first run your programs. Just to see if it would help, I tried modifying the script to run an ANALYZE against each table after 4,000 insertions, instead of breaking and re-establishing the DB connection. I still saw ~45-minute times to insert 180,000 rows. I then tried running ANALYZE against each table after *each* 4,000 rows inserted, and again, it took about 45 minutes to run the insert. Each table is empty when I first run the program. I am dropping and re-creating the database for each test run. There is clearly a problem, but it is not yet clearly a bug. If it is a bug, we're interested in solving it as much as you. I'd be happy to run further tests or provide more details, if they'll help. We now have a workaround which is allowing us to proceed with our project, but I'd like to know if there's another way to do this. While I understand that large or complex databases require careful tuning, I was surprised to see a six- or seven-fold increase in run times between PostgreSQL 7.4 and 8.0.1 on the same hardware, on an operation which seems fairly straightforward: populating an empty table. One other thing which puzzled me: as a test, I tried modifying our script to spit out raw SQL statements instead of connecting to the database and performing the inserts itself. Normally, our script populates two tables in one pass, and then populates the third and fourth tables in a second pass. I massaged the SQL by hand to group the inserts together by table, so that the first table would be entirely populated, then the second, etc. When I ran this SQL script by piping it straight into psql, it finished in about four minutes. This is comparable to the time it takes to run my modified script which breaks and re-establishes the connection to the database. It would appear that psql is doing something right here which we have had to go out of our way to get with psycopg. Keith Browne [EMAIL PROTECTED] -
Re: [BUGS] BUG #1552: massive performance hit between 7.4 and 8.0.1
Tom Lane wrote: I'm still looking for an example that demonstrates why this is a common problem that we need to worry about. ISTM that if an FK reference is hit when there are still zero entries in the referenced table, that insertion will fail anyway, and so people wouldn't try to load data in such an order. Tom, We're filling pairs of tables with rows having nearly a one-to-one mapping; very rarely, the second table will have multiple rows corresponding to one row in the first table. When we insert the first row in the second table, therefore, we've just put the corresponding row into the first table, so the foreign key constraint is satisfied. I can't say how common this sort of thing will be. It appears to me that BUG #1541 is similar to what we're seeing, and a search of the mailing lists also turns up this message: http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php which also describes symptoms similar to what I'm seeing. We could band-aid this in 8.0 as previously suggested (have the planner assume > 0 pages when it sees actually 0 pages) but without seeing a concrete example I can't tell if that will fix the complaint or not. It sounds like this could work for us, if it would disable sequential searches into a table which grows from 0 to >60,000 rows in one session. Is breaking and re-establishing the database session the best workaround, or is there a better way to provide a hint to the planner? Regards, Keith Browne [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1913: select into a table missing a row part 2
The following bug has been logged online: Bug reference: 1913 Logged by: Keith Randall Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1beta2 Operating system: Linux 2.4.21-15 Redhat enterprise Description:select into a table missing a row part 2 Details: I'm missing a row when I generate a table off a view. select count(*) from foo; 20811 select * into temp from foo; select count(*) from temp; 20810 I have scripts that you can run to replicate at https://www.oas.ca/~randallk/pgbug.tar.bz2 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #1912: select into a table missing a row
The following bug has been logged online: Bug reference: 1912 Logged by: Keith Randall Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1beta2 Operating system: Linux 2.4.21-15 Redhat enterprise Description:select into a table missing a row Details: I'm missing a row when I generate a table off a view. select count(*) from foo; 20811 select * into temp from foo; select count(*) from temp; 20810 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2072: CPPFLAGS clobbered
The following bug has been logged online: Bug reference: 2072 Logged by: Keith Randall Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.0 Operating system: Linux Description:CPPFLAGS clobbered Details: In src/template/linux the CPPFLAGS="-D_GNU_SOURCE" overrides command-line CPPFLAGS I change it to CPPFLAGS="$CPPFLAGS -D_GNU_SOURCE" and am back in business. Keith ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #6264: Superuser does not have inherent Replication permission
The following bug has been logged online: Bug reference: 6264 Logged by: Keith Fiske Email address: ke...@omniti.com PostgreSQL version: 9.1.1 Operating system: Debian Description:Superuser does not have inherent Replication permission Details: Was testing out an upgrade from 9.0.4 to 9.1.1 and ran across either some lack of clarity in the documentation or a bug in the superuser privilege. I had a streaming replication setup and after the upgrade and setting up the new slave, the slave would no longer connect to the master for SR. Looking into this more, I saw that 9.1 has a new Replication permission to allow non-superusers the ability to be replication users. The documentation says replication is inherent to a superuser. After testing several times, I can assure you it is not. The old replication user from 9.0.4 which was carried through the upgrade kept its superuser privileges, but was unable to be used for replication until I explicitly granted it the Replication permission. Brand new roles created initially as superusers are explicitly given the separate replication permission. If you create a user as a NONsuperuser, then later ALTER them to be one, they will NOT have the replication permission and cannot be used as a replication user until you explicitly grant that permission. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6264: Superuser does not have inherent Replication permission
On Sat, Oct 22, 2011 at 11:49 AM, Tom Lane wrote: > "Keith Fiske" writes: >> The documentation says replication is inherent to a superuser. > > What it actually says is that superusers get the replication privilege > by default --- but you can create a superuser without that. If you > see a place that says something different, please point it out > specifically so we can improve it. From the documentation "...If not specified, NOREPLICATION is the default for all roles except superusers..." From the 9.1 release notes: "...Previously only superusers could initiate replication connections; superusers still have this permission by default..." Reading through all the other options, none of them state anything special about superuser defaults. As stated above, if one were first reading this, one would assume that a superuser would have replication permission as default, no matter if it was given superuser via ALTER ROLE or CREATE ROLE. If you're going to start saying which permissions a superuser has and doesn't have by default, you should be consistent with all of them and clearly state how and when they are given. Ex: superusers inherently have CREATEROLE and CREATEDB, but not LOGIN. And especially reading the release notes the use of the word "still" would indicate that it works the same as it did in 9.0. It's what caused my much of my confusion in this whole thing. When I upgraded my 9.0.4 system to 9.1 and couldn't get streaming replication to work, I saw the above and began to assume this was a regression of the "replication" role name issue since it gave the same error that that bug gave and things were working fine in 9.0.4. It was only after I created a brand new superuser and then did a \du that I noticed the replication permission was tacked on separately and only during role creation. An upgrade from 9.0 to 9.1 does not account for SR users and the release notes should probably reflect this, NOT state that superusers still have the permission by default. They don't. > >> After testing >> several times, I can assure you it is not. > > Please show your test case. It looks to me like it works as expected: > > regression=# create user foo superuser; > CREATE ROLE > regression=# create user foo2 superuser noreplication; > CREATE ROLE > regression=# select rolname,rolsuper,rolreplication from pg_authid ; > rolname | rolsuper | rolreplication > --+--+ > postgres | t | t > foo | t | t > foo2 | t | f > (3 rows) > > If you create a non-super user then ALTER them to be one, they don't have replication as default. This either needs to be clarified in the documentation or the ALTER should give the same defaults as the CREATE. Whether it's inherent like CREATEDB or a separate permission, I don't think matters and is a whole other argument. But things should be consistent in some manner. >> If you create a user as a NONsuperuser, >> then later ALTER them to be one, they will NOT have the replication >> permission and cannot be used as a replication user until you explicitly >> grant that permission. > > That doesn't sound to me like a bug. These flags are independent, we > just provide a certain default at role creation time. > That is not what the documentation as read would lead people to believe. I'd be more than happy to help with clarifying the documentation myself if needed. Just let me know how. > regards, tom lane > -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6581: pg_dumpall --no-tablespaces option still sets default tablespace
On Wed, Apr 11, 2012 at 2:21 PM, Tom Lane wrote: > ke...@omniti.com writes: >> Using the --no-tablespaces option for pg_dumpall correctly stops tablespace >> creation and each individual object from having its tablespace set. But if >> the original database had its default tablespace changed, this is still >> added at the beginning of the dump file: > >> ALTER DATABASE dbname SET default_tablespace TO 'old_db_tablespace'; > > Well, what the --no-tablespaces option does is suppress actions that are > related to tablespaces. However, ALTER DATABASE SET options are just > dumped out by generic code (likewise for ALTER ROLE SET, function SET > options, yadda yadda). I guess we could insert a hack that looked for > tablespace-related options and didn't print them, but it seems like it > would be a wart not a feature. > > Also, the purpose of the option is to let you reload the dump into an > installation that hasn't got the same (or any) tablespaces. ALTER SET > commands won't prevent that, they'll at worst result in some NOTICEs > during the reload. > > In short, I'm not real sure this should be considered a bug. > > regards, tom lane Agreed it's not a serious bug and doesn't stop the restore during a basic replay with psql. But besides the warning during restore, it also causes warnings after the reload when you connect to the database. postgres=# \c dbname WARNING: invalid value for parameter "default_tablespace": "alldata1" DETAIL: Tablespace "alldata1" does not exist. You are now connected to database "dbname" as user "postgres". dbname=# Again, easily fixed with running another ALTER after the database is up and running. But figured I'd report it. -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. 443.325.1357 x251 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7716: Only owner can drop trigger?
On Fri, Nov 30, 2012 at 7:02 PM, Jeff Davis wrote: > On Fri, 2012-11-30 at 23:41 +, ke...@omniti.com wrote: >> The following bug has been logged on the website: >> >> Bug reference: 7716 >> Logged by: Keith Fiske >> Email address: ke...@omniti.com >> PostgreSQL version: 9.2.1 >> Operating system: Any >> Description: >> >> This is documented in the DROP TRIGGER section of the documentation, but it >> doesn't seem to make any sense. If a role has permission to create a >> trigger, why can that same role not have the same permissions to drop it, >> even if that role doesn't own the table? > > My guess is that it's to prevent someone from dropping a trigger created > by someone else. > > I agree that it's inconsistent. I'm not sure why they added the separate > "TRIGGER" privilege in the first place, but it was done more than 10 > years ago. > > Regards, > Jeff Davis > > > So then don't give that other role trigger permissions if you don't want them to be able to drop a trigger. I'm actually thankful for the trigger permission since it allows the extension I'm working on to create triggers on tables the role doesn't own. But now it only half-works because it can't clean up after itself when the trigger needs to be dropped. Keith -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4041: error in Application Stack Builder
I can. -Original Message- From: Dave Page [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2008 3:50 PM To: Chan, Keith (LESA IT) Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #4041: error in Application Stack Builder On Mon, Mar 17, 2008 at 10:15 PM, keith chan <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 4041 > Logged by: keith chan > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.3 > Operating system: Windows XP with SP2 > Description:error in Application Stack Builder > Details: > > I did followings > > 1. click Start at bottom-left of Windows XP and select Programs > > 2. select PostgreSQL 8.3 and Application Stack Builder > > 3. In Stack Builder dialog form, select PosgreSQL Database Server 8.3 on > port 5432 in dropdown list > > 4. click Next button. It shows a error dialog form Stack Builder Error with > message > "Failed to open the application list: > http://www.postgresql.org/applications.xml > > Error: The URL specified could not be opened." Works fine from here. Can you open the file in your browser? -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4041: error in Application Stack Builder
Can you tell me what I need to enter at HTTP proxy and Port, FTP proxy and Port? Thanks Keith -Original Message- From: Dave Page [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2008 1:28 AM To: Chan, Keith (LESA IT) Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #4041: error in Application Stack Builder On Tue, Mar 18, 2008 at 12:23 AM, Chan, Keith (LESA IT) <[EMAIL PROTECTED]> wrote: > I can. OK, please try StackBuilder again in case it was a glitch. If it still doesn't work, perhaps you need to feed it some proxy server info on the first page? -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4041: error in Application Stack Builder
I did enter them, but got the same problem. Actually what I want is to install PostGIS for the PostgreSQL 8.3. I did install PostGIS for PostgreSQL 8.2. That was easy. But PostGIS said I need to do this way to install PostGIS for PostgreSQL 8.3. I don't know why make the installation so troublesome for the version. Thanks Keith -Original Message- From: Dave Page [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2008 12:12 PM To: Chan, Keith (LESA IT) Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #4041: error in Application Stack Builder On Tue, Mar 18, 2008 at 3:52 PM, Chan, Keith (LESA IT) <[EMAIL PROTECTED]> wrote: > Can you tell me what I need to enter at HTTP proxy and Port, FTP proxy > and Port? No idea - those details (if required) are specific to your network. You should check with your local administrator. -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
