[BUGS] BUG #7920: Sequence rename leave stale value for sequence_name
The following bug has been logged on the website: Bug reference: 7920 Logged by: Maksym Boguk Email address: maxim.bo...@gmail.com PostgreSQL version: 9.2.3 Operating system: Linux Description: sequence_name left stale after sequence rename: Test case shows same problem on versions 9.0 9.1 9.2: [postgres]=# create sequence qqq; [postgres]=# SELECT sequence_name FROM qqq; sequence_name --- qqq [postgres]=# alter sequence qqq rename to lalala; --surprise [postgres]=# SELECT sequence_name FROM lalala; sequence_name --- qqq pg_dump -F p -s postgres | grep qqq --empty -- 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 #7920: Sequence rename leave stale value for sequence_name
On 2013-03-06 09:15:01 +, maxim.bo...@gmail.com wrote: The following bug has been logged on the website: Bug reference: 7920 Logged by: Maksym Boguk Email address: maxim.bo...@gmail.com PostgreSQL version: 9.2.3 Operating system: Linux Description: sequence_name left stale after sequence rename: Test case shows same problem on versions 9.0 9.1 9.2: [postgres]=# create sequence qqq; [postgres]=# SELECT sequence_name FROM qqq; sequence_name --- qqq [postgres]=# alter sequence qqq rename to lalala; --surprise [postgres]=# SELECT sequence_name FROM lalala; sequence_name --- qqq pg_dump -F p -s postgres | grep qqq --empty I don't find this particularly suprising. Nothing looks at that field in sequences, there imo is no point on having the name inside at all. Do you need that for some usecase or did you just happen to notice it? SELECT tableoid::regclass AS sequence_name FROM lalala; should do the trick for now. I personally don't see any way to nicely fix that. We can add code to also change the contents, but currently thats generic code. Or we could just remove the column in the next release? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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 #7920: Sequence rename leave stale value for sequence_name
I don't find this particularly suprising. Nothing looks at that field in sequences, there imo is no point on having the name inside at all. Do you need that for some usecase or did you just happen to notice it? I personally don't see any way to nicely fix that. We can add code to also change the contents, but currently thats generic code. Or we could just remove the column in the next release? Well, this story began with one very bad named sequence. After database structure audit the developers were asked to rename this sequence to something more appropriate. And when they performed alter ... rename they found that the name still same. After that they came to me with questions. Task is remove that very bad name from the production database altogether. It seems that the easiest way is drop sequence and create new sequece. +1 for just remove the column in the next release -- Maxim Boguk Senior Postgresql DBA http://www.postgresql-consulting.ru/ http://www.postgresql-consulting.com/ Phone RU: +7 910 405 4718 Phone AU: +61 45 218 5678 Skype: maxim.boguk Jabber: maxim.bo...@gmail.com МойКруг: http://mboguk.moikrug.ru/ People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage.
Re: [BUGS] BUG #7920: Sequence rename leave stale value for sequence_name
Andres Freund and...@2ndquadrant.com writes: On 2013-03-06 09:27:55 -0500, Tom Lane wrote: Removing the sequence_name column alone would also break existing code, for ... um ... not much. The only argument I see is reduced chance of people making errors. Code that actually uses sequence_name is broken. Well, only if you rename the sequence, which is something many people would never do. If we had something like columns that are computed on output, we could use that. What we could do is invent a new pseudo-column type like tableoid that renders as text.. In the end it doesn't seem worth bothering. Yeah. If I recall the older discussions correctly, we talked about somehow splitting a sequence's storage between transactionally-updatable and non-transactionally-updatable parts, so that we could make altering a sequence's parameters transactional. Preserving anything remotely like select * from sequence would require a view or some such. Whenever somebody gets around to attacking that whole problem, I'll be for that; but in the meantime it seems like we should leave it alone instead of making marginal changes. regards, tom lane -- 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 #7921: Problem while initializing db..initdb could not create directory..
The following bug has been logged on the website: Bug reference: 7921 Logged by: Vinny Email address: kova...@gmail.com PostgreSQL version: 9.2.1 Operating system: Windows 7 Description: When I tried to install a product that uses postgres as a database server,installation fails during the initialization of postgres db.. Error Trace: quote C:\xyz\bincall initPgsql.bat C:\xyz\bin\\..\pgsql Creating data directory Grant privileges for data directory using icacls.exe ... processed file: C:\xyz\bin\\..\pgsql\data Successfully processed 1 files; Failed processing 0 files processed file: C:\xyz\bin\\..\pgsql\data Successfully processed 1 files; Failed processing 0 files processed file: C:\xyz\bin\\..\pgsql\data Successfully processed 1 files; Failed processing 0 files Running Postgres initdb... The files belonging to this database system will be owned by user xxx. This user must also own the server process. The database cluster will be initialized with locale C. The default text search configuration will be set to english. fixing permissions on existing directory C:/xyz/bin/../pgsql/data ... ok Creating subdirectories ... initdb: could not create directory C:/xyz: File Exists initdb: removing contents of data directory C:/xyz/bin/../pgsql/data Problem while initializing DB. /quote The actual db dome folder is 'C:/xyz/pgsql'..My question is why pgsql tries to create directory outside pgsql folder that is already created?? -- 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 #7914: pg_dump aborts occasionally
Hm. Can you create a reproducible test case for this?
I think this issue happens when pg_dump is slower than the backend
for some reason.
If so, perhaps injecting a sleep() delay into the right place in pg_dump
or libpq would make it reproducible? I wouldn't have any problem
crediting a test with such modified code as being a valid test.
I think it's a good idea.
Although I can not confirm this by actually modifying the code right now,
somewhere in the loop calling PQgetCopyData() in pg_dump.c would be good.
pg_dump.c:
1348: for (;;)
1349: {
1350: ret = PQgetCopyData(conn, copybuf, 0);
1351:
1352: if (ret 0)
1353: break; /* done or error */
1354:
1355: if (copybuf)
1356: {
1357: WriteData(fout, copybuf, ret);
1358: PQfreemem(copybuf);
1359: }
sleep(1); /* maybe */
An alternative way would be running pg_dump with a lower priority.
Actually, I can reproduce this issue by setting the priority of
pg_dump to Low using Windows Task Manager
on the single processor environment.
Regards,
Shin-ichi MORITA
--
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 #7918: limitation of pagination with LIMIT and OFFSET
adrianopatr...@gmail.com adrianopatr...@gmail.com wrote: I need to process a query, the query returned as somewhere around 20 million records, I thought to do with LIMIT and OFFSET where the limit is fixed for 5000 records and will incrementing the OFFSET, but when reached OFFSET 400 000 consultation was very slow, taking almost the time of the query without LIMIT and OFFSET. You should probably use a cursor instead of LIMIT and OFFSET. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 #7914: pg_dump aborts occasionally
Shin-ichi MORITA s-mor...@beingcorp.co.jp writes: If so, perhaps injecting a sleep() delay into the right place in pg_dump or libpq would make it reproducible? An alternative way would be running pg_dump with a lower priority. Actually, I can reproduce this issue by setting the priority of pg_dump to Low using Windows Task Manager on the single processor environment. I tried nice'ing pg_dump without any success in making it bloat memory. I'm suspicious that there's something Windows-specific in what you're seeing, because it's fairly hard to credit that nobody's seen this problem in the ten years or so that libpq has been doing it like that. Can anyone else manage to reproduce a similar behavior? regards, tom lane -- 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
n = key expires in n days
nw = key expires in n weeks
nm = key expires in n months
ny = 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) heinri...@duesseldorf.de
Real name: Test Pgcrypto
Email address: t...@pgcrypto.com
Comment:
You selected this USER-ID:
Test Pgcrypto t...@pgcrypto.com
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 t...@pgcrypto.com
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 t...@pgcrypto.com
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 usage: E
[ultimate] (1). Test Pgcrypto
