Re: [BUGS] BUG #8516: Calling VOLATILE from STABLE function
On Wed, Oct 9, 2013 at 08:58:46PM +, dwa...@docketnavigator.com wrote: The following bug has been logged on the website: Bug reference: 8516 Logged by: Dwayne Towell Email address: dwa...@docketnavigator.com PostgreSQL version: 9.2.4 Operating system: CentOS Description: Why doesn't PostgreSQL give a warning when calling a volatile function from a stable function? For example: CREATE TABLE x (val double); CREATE FUNCTION g() RETURNS boolean AS $$ INSERT INTO x SELECT rand() RETURNING val0.5; $$ LANGUAGE SQL VOLATILE; CREATE FUNCTION f() RETURNS boolean AS $$ SELECT g(); -- this is where the stability-violation happens $$ LANGUAGE SQL STABLE; -- this is a lie According to the documentation, f() should be marked VOLATILE also, since calling f() produces side effects. PostgreSQL does not give a warning (or better yet, an error); I think it should. I think the answer is that function authors are required to prevent functions they mark as STABLE from calling VOLATILE functions. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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 #8467: Slightly confusing pgcrypto example in docs
On Thu, Oct 10, 2013 at 04:05:50PM -0400, Peter Eisentraut wrote: The changes shown below are incorrect, I think. On 10/2/13 12:00 PM, Bruce Momjian wrote: *** gen_salt(type text [, iter_count integer *** 353,359 entry12 years/entry /row row !entryliteralmd5//entry entry2345086/entry entry1 day/entry entry3 years/entry --- 358,364 entry12 years/entry /row row !entryliteralmd5 hash//entry Uh, the table already has a mention of md5 crypt above: entryliteralcrypt-md5//entry How can the later entry not be MD5 hash? entry2345086/entry entry1 day/entry entry3 years/entry *** gen_salt(type text [, iter_count integer *** 380,386 /listitem listitem para ! literalmd5/ numbers are from mdcrack 1.2. /para /listitem listitem --- 385,391 /listitem listitem para ! literalmd5 hash/ numbers are from mdcrack 1.2. /para /listitem listitem *** gen_random_bytes(count integer) returns *** 1343,1349 entryOpenBSD sys/crypto/entry /row row ! entryMD5 and SHA1/entry entryWIDE Project/entry entryKAME kame/sys/crypto/entry /row --- 1348,1354 entryOpenBSD sys/crypto/entry /row row ! entryMD5 hash and SHA1/entry entryWIDE Project/entry entryKAME kame/sys/crypto/entry /row Again, MD5 crypt is mentioned in the same table above: entryMD5 crypt/entry so how can this not be md5 hash? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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 #8516: Calling VOLATILE from STABLE function
On Thu, Oct 10, 2013 at 04:10:35PM -0700, Dwayne Towell wrote: According to the documentation, f() should be marked VOLATILE also, since calling f() produces side effects. PostgreSQL does not give a warning (or better yet, an error); I think it should. I think the answer is that function authors are required to prevent functions they mark as STABLE from calling VOLATILE functions. -- I understand it's an error (at least usually), my question/issue is why does PostgreSQL NOT give at least a warning when a programmer (probably accidentally) calls a VOLATILE function in one that he has specifically tagged as STABLE? The compiler has all the information to notify the programmer of a mistake, but isn't. This violates a fundamental principle of software engineering--take every opportunity to prevent errors. Well, we can't walk the function tree to know all called functions, and those they call, so we don't even try. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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 #8467: Slightly confusing pgcrypto example in docs
On Thu, Oct 10, 2013 at 08:22:30PM -0400, Peter Eisentraut wrote: On Thu, 2013-10-10 at 19:14 -0400, Bruce Momjian wrote: The changes shown below are incorrect, I think. On 10/2/13 12:00 PM, Bruce Momjian wrote: *** gen_salt(type text [, iter_count integer *** 353,359 entry12 years/entry /row row !entryliteralmd5//entry entry2345086/entry entry1 day/entry entry3 years/entry --- 358,364 entry12 years/entry /row row !entryliteralmd5 hash//entry Uh, the table already has a mention of md5 crypt above: entryliteralcrypt-md5//entry How can the later entry not be MD5 hash? Because what you pass to the functions is 'md5', not 'md5 hash', which is what the new text appears to indicate. So if we revert, will it still be clear what is MD5 and what is MD5 hash? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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 #8467: Slightly confusing pgcrypto example in docs
On Thu, Oct 10, 2013 at 08:32:32PM -0400, Bruce Momjian wrote: How can the later entry not be MD5 hash? Because what you pass to the functions is 'md5', not 'md5 hash', which is what the new text appears to indicate. So if we revert, will it still be clear what is MD5 and what is MD5 hash? I mean, will it be clear what is MD5 crypt and what is MD5 hash? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Installing/Upgrading PostgreSQL 9.1.6 to 9.3 known bugs?
On Mon, Oct 7, 2013 at 08:07:42AM -0700, fburg...@radiantblue.com wrote: Bruce, Proposed Steps. Do they look feasible? 1.) pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f /somepath/ testdb.backup testdb 2.) CREATE DATABASE newdb TEMPLATE=template_postgis; 3.) perl ../postgis-postgres/postgis-2.1.1/utils/postgis_restore.pl /somepath/ testdb.backup | psql -h localhost -p 5432 -U postgres newdb 2 errors.txt - this step may run 5-6 days, since our backup runs that long, right? 4.) At this point we will have two 6.1TB databases, so it looks like a prerequisite is to have available double the db size in disk space, right? 5.) then if no critical errors, there will be errors since we have our testdb schema in the public folder 5a.) ALTER DATABASE testdb RENAME TO olddb; 5b.) ALTER DATABASE newdb RENAME TO testdb; 6.) At this point hopefully we should be upgraded from postgis 1.5.3 to postgis 2.1.1, with PostgreSQL 9.1.6 7.) then can we just use pg_upgrade with the hard links option, instead of copying files to the new cluster option to upgrade to PostgreSQL 9.3? Sorry, I have no idea how to upgrade PostGIS. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #8467: Slightly confusing pgcrypto example in docs
On Wed, Oct 2, 2013 at 12:00:44PM -0400, Bruce Momjian wrote: Based on your report, I have developed the attached doc patch which clarifies when MD5 hash is being referenced, and when MD5 crypt is. I have also added your other suggestions. Patch applied, and backpatched to 9.3.X. Thanks for the suggestions. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #8469: Xpath behaviour unintuitive / arguably wrong
On Fri, Oct 4, 2013 at 10:20:46PM +0200, Stefan Kaltenbrunner wrote: http://www.postgresql.org/message-id/201106291934.23089.rsmog...@softperience.eu There are two other similar bug reports on this from February and March of this year: http://www.postgresql.org/message-id/e1u1fkl-0002rd...@wrihigleys.postgresql.org I think that should be: http://www.postgresql.org/message-id/e1u1fkl-0002rd...@wrigleys.postgresql.org http://www.postgresql.org/message-id/e1uhyuw-0001oj...@wrigleys.postgresql.org Someone who knows XML needs to take leadership on this and propose a patch. Added to TODO. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] psql \i command with a missing file name doesn't rollback the transaction
On Fri, Sep 20, 2013 at 11:15:01AM +0200, Alexey Klyukin wrote: Hi, We've discovered a surprising behavior of psql \i command. What we sometimes to add new tables to the database is: begin; \i /path/to/table/definitions/table1.sql \i /path/to/table/definitions/table2.sql ... \i /path/to/table/definitions/tableN.sql commit; What we discovered that some files in the /path/to/table/definitions were missing (say, table 2,3), but the table 1, 4... N appeared in the database after executing the transaction. This is quite a catch, since we cannot rely on transaction consistency when using an include directive. The test is simple: begin; \i whatever; select 1; commit; The expected behavior was that select 1 would lead to 'ERROR: current transaction is aborted'. The current behavior is that it is executed, although a message is emitted to a client: whatever: No such file or directory Would it be possible from the client side to generate the rollback to the server on an attempt to include a non-existing file (perhaps only when ON_ERROR_STOP is set to 1?). The problem is how would we decide what psql actions should trigger a rollback, and how would we show the user we did that. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Installing/Upgrading PostgreSQL 9.1.6 to 9.3 known bugs?
On Fri, Sep 20, 2013 at 02:00:05PM -0700, John R Pierce wrote: On 9/20/2013 1:51 PM, fburg...@radiantblue.com wrote: 1.) During our prior upgrade process we used pg_upgrade to move from pg 8.4.3 to 9.1.6 using the hard links install option, we also have our data spread across disk storage mediums; fiber, nas. Are there any known issues, bugs with using pg_upgrade to move from 9.1.6 to pg 9.3? having multiple tablespaces on different mount points complicates things. I'm not sure how or if pg_upgrade handles that. pg_upgrade is fine with that. The old/new tablespaces stay in the same mount point as just per-version subdirectories. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #8467: Slightly confusing pgcrypto example in docs
On Tue, Sep 24, 2013 at 11:20:55PM +0100, Richard Neill wrote:
I'm sorry about that: I think I need to correct my proposed
correction! I think I've been writing too much C recently, and so I
foolishly mis-read that as returning pswhash, rather than returning
the truth of the comparison.
What I meant to write, for clarity, was:
SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;
which would make it obvious that we're returning the boolean named pswmatch.
[Also, should the default example perhaps use gen_salt('bf'), as opposed to
gen_salt('md5') ?]
This, however, might be a good idea. People should of course always
read the documentation, but having the examples including the best
practice would probably be a good idea.
Incidentally, there are 2 other things that confused me in this section.
1. Table F-18. Supported algorithms for crypt() has a column
labelled max password length. It would perhaps also be useful to
know the size of column needed to store the crypted password (my
original crypt using md5 easily fits in a varchar(70), whereas using
bf needs the column to be varchar(100).)
2. Table F-20. Hash algorithm speeds
What's the difference here between crypt-md5 and md5 ?
If I've rightly read this, the algorithm named md5 in the crypt()
documentation is named crypt-md5 here, whereas Table F20's md5
algorithm seems to refer to something else - probably the normal
version of md5.
If so, it would be clearer to write that the last 2 lines (md5 and
sha1) are for comparison only, and refer to the speed of doing an
ordinary md5/sha1 sum, rather than the md5-variant of crypt().
Anyway, thanks again for your help - Postgres is a wonderful system,
which I've found to be repeatedly useful.
Based on your report, I have developed the attached doc patch which
clarifies when MD5 hash is being referenced, and when MD5 crypt is. I
have also added your other suggestions.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/pgcrypto.sgml b/doc/src/sgml/pgcrypto.sgml
new file mode 100644
index a0eead7..976c7db
*** a/doc/src/sgml/pgcrypto.sgml
--- b/doc/src/sgml/pgcrypto.sgml
*** hmac(data bytea, key text, type text) re
*** 95,102
/para
para
!The algorithms in functioncrypt()/ differ from usual hashing algorithms
!like MD5 or SHA1 in the following respects:
/para
orderedlist
--- 95,102
/para
para
!The algorithms in functioncrypt()/ differ from the usual
!MD5 or SHA1 hashing algorithms in the following respects:
/para
orderedlist
*** hmac(data bytea, key text, type text) re
*** 142,147
--- 142,148
entryMax Password Length/entry
entryAdaptive?/entry
entrySalt Bits/entry
+ entryOutput length/entry
entryDescription/entry
/row
/thead
*** hmac(data bytea, key text, type text) re
*** 151,156
--- 152,158
entry72/entry
entryyes/entry
entry128/entry
+ entry60/entry
entryBlowfish-based, variant 2a/entry
/row
row
*** hmac(data bytea, key text, type text) re
*** 158,163
--- 160,166
entryunlimited/entry
entryno/entry
entry48/entry
+ entry34/entry
entryMD5-based crypt/entry
/row
row
*** hmac(data bytea, key text, type text) re
*** 165,170
--- 168,174
entry8/entry
entryyes/entry
entry24/entry
+ entry20/entry
entryExtended DES/entry
/row
row
*** hmac(data bytea, key text, type text) re
*** 172,177
--- 176,182
entry8/entry
entryno/entry
entry12/entry
+ entry13/entry
entryOriginal UNIX crypt/entry
/row
/tbody
*** UPDATE ... SET pswhash = crypt('new pass
*** 205,211
para
Example of authentication:
programlisting
! SELECT pswhash = crypt('entered password', pswhash) FROM ... ;
/programlisting
This returns literaltrue/ if the entered password is correct.
/para
--- 210,216
para
Example of authentication:
programlisting
! SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;
/programlisting
This returns literaltrue/ if the entered password is correct.
/para
*** gen_salt(type text [, iter_count integer
*** 353,359
entry12 years/entry
/row
row
!entryliteralmd5//entry
entry2345086/entry
entry1 day/entry
entry3 years/entry
--- 358,364
entry12 years/entry
/row
row
!entryliteralmd5 hash//entry
entry2345086/entry
entry1 day/entry
entry3
Re: [BUGS] BUG #8469: Xpath behaviour unintuitive / arguably wrong
On Tue, Sep 24, 2013 at 06:43:19PM +, dennis.noord...@helsinki.fi wrote:
The following bug has been logged on the website:
Bug reference: 8469
Logged by: Dennis
Email address: dennis.noord...@helsinki.fi
PostgreSQL version: 9.3.0
Operating system: FreeBSD 9.2-RC4
Description:
Hi,
After upgrading an 8.1 version to 9.3.0 I am suddenly seeing text fields
containing amp; where they are populated from XML. This may be a
coincidence and the problem may have existed earlier, in any case, now I
noticed.
I extract the text content of XML nodes using xpath, from something like:
nameJones amp; Smith/name
The reason I end up with amp; is the IMHO rather odd xpath behaviour:
# select xpath('/a/text()', (select xmlelement(name a, 'A B')));
xpath
---
{A amp; B}
The canonical contents of a is A B. At first search I've found some
rather heated debates about this with bits of name calling; I certainly do
not want to get into that and I apologize in advance to those who feel very
strongly about this.
I've seen one fix describe the problem as:
DESCRIPTION: Submitter invokes following statement:
SELECT (XPATH('/*/text()', 'rootlt;/root'))[1].
He expect (escaped) result lt;, but gets
With respect, this bug makes no sense as this produces in fact the right
result. The actual value of root is , it's just escaped when serialized
to XML. If root were to actually contain lt;, it'd be serialized as
amp;lt;. It should not be possible to be blindly cast to a text type, but
explicitly serialized as such.
At least the reviewer at:
http://www.postgresql.org/message-id/201106291934.23089.rsmog...@softperience.eu
There are two other similar bug reports on this from February and March
of this year:
http://www.postgresql.org/message-id/e1u1fkl-0002rd...@wrihigleys.postgresql.org
http://www.postgresql.org/message-id/e1uhyuw-0001oj...@wrigleys.postgresql.org
Someone who knows XML needs to take leadership on this and propose a
patch.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] pg_upgrade 9.0-9.2 failure: Mismatch of relation OID in database
On Thu, Sep 26, 2013 at 02:59:30PM +0200, Christoph Berg wrote:
On upgrading a 9.0 database to 9.2 using pg_upgrade, I got this:
# pg_upgradecluster -m upgrade 9.0 main /psql/data-9.2
[...]
Performing Upgrade
--
[...]
Restoring database schema to new clusterok
Removing support functions from new cluster ok
Copying user relation files
Mismatch of relation OID in database hisrm: old OID 18804, new OID 18803
Failure, exiting
Error: pg_upgrade run failed
This is a cluster that was running with 9.0.12 (compiled locally). For
the upgrade, I installed postgresql-9.0 and -9.2 from
apt.postgresql.org (9.0.13, 9.2.4), so pg_upgrade was using these
versions. OS is Ubuntu 12.04 amd64 now and was 8.04 while the cluster
was still running on 9.0.12.
In the 9.0 cluster, 18804 is the relation oid of glm_lrahm_to_se.
pg_upgrade_dump_all.sql:
--
-- Name: glm_lrahm_to_se; Type: TABLE; Schema: mbs; Owner: fsv; Tablespace:
--
-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('18806'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT binary_upgrade.set_next_array_pg_type_oid('18805'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_class oids
SELECT binary_upgrade.set_next_heap_pg_class_oid('18804'::pg_catalog.oid);
CREATE TABLE glm_lrahm_to_se (
id integer NOT NULL,
lrahm integer NOT NULL,
se integer NOT NULL
);
That is very interesting, and it certainly should not be failing.
I am surprised it got an oid that was one less than the desired one,
18803. Is there any mention of 18803 in the SQL file? If you create a
cluster with just your schema and no data, can you upgrade that cleanly?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #7836: COPY command does not honor 'FORMAT' option
On Tue, Jan 29, 2013 at 05:52:16PM -0500, Tom Lane wrote: Jeff Janes jeff.ja...@gmail.com writes: I figured out the need for the parenthesis by reading the docs. The need to also quote 'binary' really took me by surprise, especially as the other two formats don't need to be quoted. I assume the reason for this is the level of reservedness of the various words in bison. Yeah, BINARY is a type_func_name_keyword and hence not covered by the copy_generic_opt_arg production. But is this a doc bug? Should literalbinary/literal be shown quoted if it needs to be used quote? Or at least maybe the Example section could include an example of the use of FORMAT. Probably better to see if we can fix the grammar instead of documenting a klugy workaround. I'm not sure we can easily allow fully-reserved words there, but type_func_name_keyword might work. FYI, the need for single-quotes around 'binary' was removed with a patch and backpatched. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7844: xpath missing entity decoding - bug or feature
On Fri, Feb 1, 2013 at 12:02:41PM +, i...@fduerr.de wrote:
The following bug has been logged on the website:
Bug reference: 7844
Logged by: fduerr
Email address: i...@fduerr.de
PostgreSQL version: 9.2.2
Operating system: Debian
Description:
Up until 9.1
select (xpath('/z/text()', ('z' || 'ATamp;T' || '/z')::xml))[1];
returned 'ATT'
9.2 returns 'ATamp;T'
Is it a bug or a feature?
Is there a function to decode xml-entities?
Does anyone have a comment on this?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #7858: Inaccurate Log Message
On Fri, Feb 8, 2013 at 06:46:29AM +, jackie.qq.zh...@gmail.com wrote:
The following bug has been logged on the website:
Bug reference: 7858
Logged by: Jackie Zhang
Email address: jackie.qq.zh...@gmail.com
PostgreSQL version: 9.1.4
Operating system: Ubuntu
Description:
Hi,
The log message of the sanity check for superuser_reserved_connections in
PostmasterMain() is not accurate. See the following code snippet:
/*src/backend/postmaster/postmaster.c*/
730 /*
731 * Check for invalid combinations of GUC settings.
732 */
733 if (ReservedBackends = MaxBackends)
734 {
735 write_stderr(%s: superuser_reserved_connections must
be less than max_connections\n, progname);
736 ExitPostmaster(1);
737 }
The problem is that MaxBackends is calculated by both
autovacuum_max_workers and max_connections. See assign_maxconnections()
and assign_autovacuum_max_workers() in src/backend/utils/misc/guc.c.
The log message should inform both the two configuration parameters instead
of only one -- the root cause could be the oversized
autovacuum_max_workers.
The calculation, ReservedBackends = MaxBackends, was corrected in
Postgres 9.3 with this commit:
commit b3055ab4fb5839a872bfe354b2b5ac31e6903ed6
Author: Magnus Hagander mag...@hagander.net
Date: Fri Aug 10 14:49:03 2012 +0200
Fix upper limit of superuser_reserved_connections, add limit for
wal_senders
Should be limited to the maximum number of connections excluding
autovacuum workers, not including.
Add similar check for max_wal_senders, which should never be higher
than
max_connections.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #7817: psql does not relate to footer settings in extended mode
Applied.
---
On Mon, Sep 9, 2013 at 09:16:05PM -0400, Bruce Momjian wrote:
On Sun, Jan 20, 2013 at 10:33:37AM +, emes...@redhat.com wrote:
The following bug has been logged on the website:
Bug reference: 7817
Logged by: Eli Mesika
Email address: emes...@redhat.com
PostgreSQL version: 9.1.7
Operating system: Fedora 16
Description:
psql does not relate to footer settings in extended mode
Sometimes we need to run a sql command withot generating header and footer.
This can be done using the -t flag and --pset=footer=off
The problem is that the footer is still diaplyed even if it was set to off
if we use the extended mode for the query (-x flag)
Steps to Reproduce:
1) create a table without any data
for example
create table text(i int);
2) run
psql -U user -t --pset=footer=off db
3) No output is generated
4) run
psql -U user -t --pset=footer=off -x db
5) Output generated : (No Rows)
Actual results:
psql does not honour the footer settings when output is defined to be in
Extended Mode
Expected results:
psql should not generate any output is query has no results and -t and
--pset=footer=off were given
This has been fixed in PG 9.3 (released today) for the specific options
you supplied:
$ psql -t --pset=footer=off test
Default footer is off.
psql (9.4devel)
Type help for help.
CREATE TABLE test (x INT);
CREATE TABLE
SELECT * FROM test;
\x
Expanded display is on.
SELECT * FROM test;
Unfortunately, this did not fix the more simple case where
--pset=footer=off is specified, but not -t:
$ psql --pset=footer=off test
Default footer is off.
psql (9.4devel)
Type help for help.
CREATE TABLE test (x INT);
CREATE TABLE
SELECT * FROM test;
x
---
\x
Expanded display is on.
SELECT * FROM test;
-- (No rows)
The attached patch fixes this, and makes it match the rest of the output
formats, which do honor --pset=footer=off alone for footers.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
new file mode 100644
index 5589cea..736225c
*** a/src/bin/psql/print.c
--- b/src/bin/psql/print.c
*** print_aligned_vertical(const printTableC
*** 1171,1177
if (cont-cells[0] == NULL cont-opt-start_table
cont-opt-stop_table)
{
! if (!opt_tuples_only)
fprintf(fout, _((No rows)\n));
return;
}
--- 1171,1177
if (cont-cells[0] == NULL cont-opt-start_table
cont-opt-stop_table)
{
! if (!opt_tuples_only cont-opt-default_footer)
fprintf(fout, _((No rows)\n));
return;
}
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #7817: psql does not relate to footer settings in extended mode
On Sun, Jan 20, 2013 at 10:33:37AM +, emes...@redhat.com wrote:
The following bug has been logged on the website:
Bug reference: 7817
Logged by: Eli Mesika
Email address: emes...@redhat.com
PostgreSQL version: 9.1.7
Operating system: Fedora 16
Description:
psql does not relate to footer settings in extended mode
Sometimes we need to run a sql command withot generating header and footer.
This can be done using the -t flag and --pset=footer=off
The problem is that the footer is still diaplyed even if it was set to off
if we use the extended mode for the query (-x flag)
Steps to Reproduce:
1) create a table without any data
for example
create table text(i int);
2) run
psql -U user -t --pset=footer=off db
3) No output is generated
4) run
psql -U user -t --pset=footer=off -x db
5) Output generated : (No Rows)
Actual results:
psql does not honour the footer settings when output is defined to be in
Extended Mode
Expected results:
psql should not generate any output is query has no results and -t and
--pset=footer=off were given
This has been fixed in PG 9.3 (released today) for the specific options
you supplied:
$ psql -t --pset=footer=off test
Default footer is off.
psql (9.4devel)
Type help for help.
CREATE TABLE test (x INT);
CREATE TABLE
SELECT * FROM test;
\x
Expanded display is on.
SELECT * FROM test;
Unfortunately, this did not fix the more simple case where
--pset=footer=off is specified, but not -t:
$ psql --pset=footer=off test
Default footer is off.
psql (9.4devel)
Type help for help.
CREATE TABLE test (x INT);
CREATE TABLE
SELECT * FROM test;
x
---
\x
Expanded display is on.
SELECT * FROM test;
-- (No rows)
The attached patch fixes this, and makes it match the rest of the output
formats, which do honor --pset=footer=off alone for footers.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/psql/print.c b/src/bin/psql/print.c
new file mode 100644
index 5589cea..736225c
*** a/src/bin/psql/print.c
--- b/src/bin/psql/print.c
*** print_aligned_vertical(const printTableC
*** 1171,1177
if (cont-cells[0] == NULL cont-opt-start_table
cont-opt-stop_table)
{
! if (!opt_tuples_only)
fprintf(fout, _((No rows)\n));
return;
}
--- 1171,1177
if (cont-cells[0] == NULL cont-opt-start_table
cont-opt-stop_table)
{
! if (!opt_tuples_only cont-opt-default_footer)
fprintf(fout, _((No rows)\n));
return;
}
--
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 #7824: documentation bug: Extract DOW
On Tue, Jan 22, 2013 at 08:40:30PM +, tim.rom...@yahoo.com wrote: The following bug has been logged on the website: Bug reference: 7824 Logged by: Tim Romano Email address: tim.rom...@yahoo.com PostgreSQL version: 9.2.2 Operating system: Windows 7 x64 Description: Documentation seems to indicate that only a Timestamp value is a valid argument to the Extract function when trying to get the DOW, but a Date also works: dow The day of the week (0 - 6; Sunday is 0) (for timestamp values only) SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 5 [Sorry for the late reply.] I assume you mean: SELECT EXTRACT(DOW FROM DATE '2001-02-16'); date_part --- 5 (1 row) That does work fine, and is documented in PG 9.2: http://www.postgresql.org/docs/9.2/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, -- time, or interval. (Expressions of type date are cast to timestamp and -- can therefore be used as well.) Where did you see that DATE is not supported for EXTRACT? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7730: intarray representation of empty arrays
Applied.
---
On Wed, Sep 4, 2013 at 03:01:50PM -0400, Bruce Momjian wrote:
On Wed, Dec 5, 2012 at 12:44:39AM +, el...@varlena.com wrote:
The following bug has been logged on the website:
Bug reference: 7730
Logged by: elein
Email address: el...@varlena.com
PostgreSQL version: 9.2.1
Operating system: Linux
Description:
select NULLIF('{1,2,3}'::integer[] - '{3,2,1}'::integer[], '{}'::integer[]);
This returns an empty array. It should return NULL.
Per RhodiumToad: the core code represents '{}' as an array with 0
dimensions, whereas intarray represents it as an array with 1 dimension but
0 elements
intarray should use the same standards as the core code if possible. I
peered at the code and don't see anything untoward but did not have time to
spend on it.
I just got time to look at this, and it is certainly easier to see when
you use array_dims():
SELECT '{1,2,3}'::integer[] - '{3,2,1}'::integer[];
?column?
--
{}
SELECT array_dims('{1,2,3}'::integer[] - '{3,2,1}'::integer[]);
array_dims
[1:0]
SELECT array_dims('{}'::integer[]);
array_dims
(null)
This is part of the larger TODO item of how to handle empty
=1-dimensional empty arrays vs. zero-dimensional empty arrays, which is
discussed here:
https://wiki.postgresql.org/wiki/Todo#Arrays
Improve handling of empty arrays
In that thread, no one could find a way to create a 1-dimensional empty
array at the SQL level, but thanks to intarray, you found a way. It is
natural that intarray, being mostly used for one-dimensional arrays,
would return a 1-dimensional empty array. However, besides being
inconsistent, as you mentioned, there is also no way to dump/restore
one-dimensional empty arrays, which is a larger concern.
I have developed the attached patch to force empty intarray results to
be zero-dimensional empty arrays, rather than 1-dimensional empty
arrays. With this patch, a zero-dimensional empty array is returned:
SELECT array_dims('{1,2,3}'::integer[] - '{3,2,1}'::integer[]);
array_dims
(null)
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/intarray/_int_tool.c b/contrib/intarray/_int_tool.c
new file mode 100644
index 8635576..fc462b2
*** a/contrib/intarray/_int_tool.c
--- b/contrib/intarray/_int_tool.c
*** resize_intArrayType(ArrayType *a, int nu
*** 246,251
--- 246,258
int nbytes = ARR_DATA_OFFSET(a) + sizeof(int) * num;
int i;
+ /* if no elements, return a zero-dimensional array */
+ if (num == 0)
+ {
+ ARR_NDIM(a) = 0;
+ return a;
+ }
+
if (num == ARRNELEMS(a))
return a;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #7730: intarray representation of empty arrays
On Wed, Dec 5, 2012 at 12:44:39AM +, el...@varlena.com wrote:
The following bug has been logged on the website:
Bug reference: 7730
Logged by: elein
Email address: el...@varlena.com
PostgreSQL version: 9.2.1
Operating system: Linux
Description:
select NULLIF('{1,2,3}'::integer[] - '{3,2,1}'::integer[], '{}'::integer[]);
This returns an empty array. It should return NULL.
Per RhodiumToad: the core code represents '{}' as an array with 0
dimensions, whereas intarray represents it as an array with 1 dimension but
0 elements
intarray should use the same standards as the core code if possible. I
peered at the code and don't see anything untoward but did not have time to
spend on it.
I just got time to look at this, and it is certainly easier to see when
you use array_dims():
SELECT '{1,2,3}'::integer[] - '{3,2,1}'::integer[];
?column?
--
{}
SELECT array_dims('{1,2,3}'::integer[] - '{3,2,1}'::integer[]);
array_dims
[1:0]
SELECT array_dims('{}'::integer[]);
array_dims
(null)
This is part of the larger TODO item of how to handle empty
=1-dimensional empty arrays vs. zero-dimensional empty arrays, which is
discussed here:
https://wiki.postgresql.org/wiki/Todo#Arrays
Improve handling of empty arrays
In that thread, no one could find a way to create a 1-dimensional empty
array at the SQL level, but thanks to intarray, you found a way. It is
natural that intarray, being mostly used for one-dimensional arrays,
would return a 1-dimensional empty array. However, besides being
inconsistent, as you mentioned, there is also no way to dump/restore
one-dimensional empty arrays, which is a larger concern.
I have developed the attached patch to force empty intarray results to
be zero-dimensional empty arrays, rather than 1-dimensional empty
arrays. With this patch, a zero-dimensional empty array is returned:
SELECT array_dims('{1,2,3}'::integer[] - '{3,2,1}'::integer[]);
array_dims
(null)
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/intarray/_int_tool.c b/contrib/intarray/_int_tool.c
new file mode 100644
index 8635576..fc462b2
*** a/contrib/intarray/_int_tool.c
--- b/contrib/intarray/_int_tool.c
*** resize_intArrayType(ArrayType *a, int nu
*** 246,251
--- 246,258
int nbytes = ARR_DATA_OFFSET(a) + sizeof(int) * num;
int i;
+ /* if no elements, return a zero-dimensional array */
+ if (num == 0)
+ {
+ ARR_NDIM(a) = 0;
+ return a;
+ }
+
if (num == ARRNELEMS(a))
return a;
--
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 #7754: Contrib start scipt comment refers to dead URL
On Sat, Dec 15, 2012 at 03:31:55AM +, pg-...@snkmail.com wrote: The following bug has been logged on the website: Bug reference: 7754 Logged by: Gavan Schneider Email address: pg-...@snkmail.com PostgreSQL version: 9.2.2 Operating system: OSX Description: FILE: postgresql-9.2.2/contrib/start-scripts/osx/PostgreSQL ; and many previous versions PBOBLEM: The comment: # For more information on Darwin/Mac OS X startup bundles, see this article: # # http://www.opensource.apple.com/projects/documentation/howto/html/SystemStarter_HOWTO.html # refers to a dead link. I wrote to Apple who confirmed it was dead, implicitly declined my request for a redirection, and suggested I report here. Suggest this bit of text be snipped as the underlying methodology has been deprecated for years, and is likely to be dropped soon. Specifically there seems little point educating those who don't already know this method. Done. The fix will appear in 9.3.1. OBSERVATION: I am thinking this contribution should be enhanced/replaced with a method suitable for current versions of OSX? So far I have not found a working plist recipe for my situation (and I have tried quite a few) so feel poorly placed to offer anything just now. Happy to trial ideas as suggested. Esp. since I seem to have invented a 'tough' environment :( Could you point me to an active postgresql discussion of this, so I can get up to speed (still pretty new to the pg community). Sorry, I have not seen this discussed anywhere. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7799: Several configuration options in guc.c miss descriptions
Thanks. This will appear in PG 9.4.
---
On Tue, Jan 8, 2013 at 07:24:57AM +, t...@cs.ucsd.edu wrote:
The following bug has been logged on the website:
Bug reference: 7799
Logged by: Tianyin Xu
Email address: t...@cs.ucsd.edu
PostgreSQL version: 9.2.2
Operating system: any
Description:
Hi, I just noticed that several developer options do not have descriptions
(in src/backend/utils/misc/guc.c) with the text No description available.
I wrote the desc. according to the online doc, see below.
HTH
--- src/backend/utils/misc/guc.c
+++ src/backend/utils/misc/guc.c.
@@ -988,7 +988,7 @@ static struct config_bool ConfigureNamesBool[] =
#ifdef BTREE_BUILD_STATS
{
{log_btree_build_stats, PGC_SUSET, DEVELOPER_OPTIONS,
- gettext_noop(No description available.),
+ gettext_noop(Logs system resource usage statistics
(memory and CPU) on
various B-tree operations.),
NULL,
GUC_NOT_IN_SAMPLE
},
@@ -1062,7 +1062,7 @@ static struct config_bool ConfigureNamesBool[] =
#ifdef LOCK_DEBUG
{
{trace_locks, PGC_SUSET, DEVELOPER_OPTIONS,
- gettext_noop(No description available.),
+ gettext_noop(Emits information about lock usage.),
NULL,
GUC_NOT_IN_SAMPLE
},
@@ -1072,7 +1072,7 @@ static struct config_bool ConfigureNamesBool[] =
},
{
{trace_userlocks, PGC_SUSET, DEVELOPER_OPTIONS,
- gettext_noop(No description available.),
+ gettext_noop(Emits information about user lock
usage.),
NULL,
GUC_NOT_IN_SAMPLE
},
@@ -1082,7 +1082,7 @@ static struct config_bool ConfigureNamesBool[] =
},
{
{trace_lwlocks, PGC_SUSET, DEVELOPER_OPTIONS,
- gettext_noop(No description available.),
+ gettext_noop(Emits information about lightweight lock
usage.),
NULL,
GUC_NOT_IN_SAMPLE
},
@@ -1092,7 +1092,7 @@ static struct config_bool ConfigureNamesBool[] =
},
{
{debug_deadlocks, PGC_SUSET, DEVELOPER_OPTIONS,
- gettext_noop(No description available.),
+ gettext_noop(Dumps information about all current locks
when a deadlock
timeout occurs.),
NULL,
GUC_NOT_IN_SAMPLE
},
@@ -1828,8 +1828,8 @@ static struct config_int ConfigureNamesInt[] =
#ifdef LOCK_DEBUG
{
{trace_lock_oidmin, PGC_SUSET, DEVELOPER_OPTIONS,
- gettext_noop(No description available.),
- NULL,
+ gettext_noop(Sets the minimum OID of tables for
tracking locks.),
+ gettext_noop(Is used to avoid output on system
tables.),
GUC_NOT_IN_SAMPLE
},
Trace_lock_oidmin,
@@ -1838,7 +1838,7 @@ static struct config_int ConfigureNamesInt[] =
},
{
{trace_lock_table, PGC_SUSET, DEVELOPER_OPTIONS,
- gettext_noop(No description available.),
+ gettext_noop(Sets the OID of the table with
unconditionally lock
tracing.),
NULL,
GUC_NOT_IN_SAMPLE
},
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #7652: Documentation index lacks functions from Additional Supplied Modules
On Sun, Nov 11, 2012 at 06:35:35PM +, andreas.hei...@web.de wrote: The following bug has been logged on the website: Bug reference: 7652 Logged by: Andreas H. Email address: andreas.hei...@web.de PostgreSQL version: 9.2.1 Operating system: any Description: It seems, that none of the functions in the section Additional Supplied Modules are listed in the index. I checked both the intagg module and the dblink module. To bad if you are looking the documentation of a function but don't know which module provides it (if any at all). FYI, this will be corrected in 9.3. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #8335: trim() un-document behaviour
On Fri, Aug 9, 2013 at 11:06:15AM -0400, Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
The attached patch swaps the arguments in the parser, and allows your
expected behavior:
This patch is completely unsafe. It will break stored rules, which may
contain calls using the existing argument order (which will be dumped
without any of the SQL-spec syntactic sugar). To say nothing of existing
applications that may be relying on calling the underlying functions with
their existing argument order.
The inconsistency in argument order is unfortunate but we're long since
stuck with it, I'm afraid.
Yes, I have thought about this some more and another problem is that
rtrim/btrim/ltrim() use the source string first, so having trim() have
the source string second when using a comma is very confusing, e.g.:
-- with patch
SELECT trim('x', 'xabcx');
btrim
---
abc
-- btrim
SELECT btrim('xabcx', 'x');
btrim
---
abc
I think we can either document what we have, or remove the ability to
use comma with trim(). If we go with documentation, it is going to look
confusing as the optional modifier is going to be on the source string,
e.g.:
SELECT trim(both 'xabcx', 'x');
btrim
---
abc
We could modify the grammar to force the modifier on the second
argument, but that is more parser states for limited value.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #8335: trim() un-document behaviour
On Fri, Jul 26, 2013 at 02:23:10AM +, am...@amutu.com wrote:
in the postgresql doc 9.4,I find the trim() function like this:
trim([leading | trailing | both] [characters] from string)
so the trim should be pass only one argument with some optional prefix ---
but I
find the following calls with two argument is successfull but the results is
unexpected and wired:
##first call
postgres=# select trim(trailing '/, 'fasd/');
rtrim
--
(1 row)
-!!!note: it return titile is rtrim
## second call
postgres=# select trim('/', 'fasd/')
;
btrim
-
(1 row)
-!!!note: it return titile is btrim
it seems trim is transform to rtrim internal but the above call should
return error or it may produce un-expect results
(I have cleaned up this posting because single-quotes were converted to
Unicode forward-backward quotes):
What is happening is that TRIM() is converted by the parser to calls to
base functions, e.g.
\df *trim*
List of functions
Schema | Name | Result data type | Argument data types | Type
+---+--+-+
pg_catalog | btrim | bytea| bytea, bytea| normal
pg_catalog | btrim | text | text| normal
pg_catalog | btrim | text | text, text | normal
pg_catalog | ltrim | text | text| normal
pg_catalog | ltrim | text | text, text | normal
pg_catalog | rtrim | text | text| normal
pg_catalog | rtrim | text | text, text | normal
That is why the headings don't say 'trim', but 'btrim', or similar ---
not sure we can easily improve that, and you can change the label with
AS.
The larger problem is the use of ',' instead of FROM, and the backwards
interpretation of the arguments. The query:
SELECT trim('/' FROM 'fasd/')
is internally converted to:
SELECT btrim('fasd/', '/')
Note the arguments are reversed. The comma syntax does not reverse the
arguments:
SELECT trim('/', 'fasd/')
is internally converted to:
SELECT btrim('/', 'fasd/')
You can even use modifiers like TRAILING with comma syntax:
SELECT trim(TRAILING '/', 'fasd/');
and that uses 'rtrim', but of course the behavior is still reverse of
expected.
Basically the odd comma behavior is because without a FROM, the
arguments are passed directly to btrim/rtrim/ltrim, and these functions
take the origin string first, then the string of characters to remove.
You are right this is undocumented.
The attached patch swaps the arguments in the parser, and allows your
expected behavior:
SELECT trim('x', 'xfasdx');
btrim
---
fasd
Another option would be to change the C API for the b/r/ltrim functions,
or disallow the use of the comma TRIM syntax in the parser.
I am a little worried people might be relying on the trim/comma syntax
somewhere.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index 22e82ba..8419559
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*** substr_for: FOR a_expr{ $$ = $2;
*** 11993,11999
trim_list: a_expr FROM expr_list { $$ = lappend($3, $1); }
| FROM expr_list { $$ = $2; }
! | expr_list{ $$ = $1; }
;
in_expr: select_with_parens
--- 11993,12000
trim_list: a_expr FROM expr_list { $$ = lappend($3, $1); }
| FROM expr_list { $$ = $2; }
! | a_expr ',' a_expr { $$ = list_make2($3, $1); }
! | a_expr{ $$ = list_make1($1); }
;
in_expr: select_with_parens
--
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 #8293: There are no methods to convert json scalar text to text in v9.3 beta2
On Wed, Jul 10, 2013 at 07:07:54PM +, jaroslav.pota...@gmail.com wrote:
The following bug has been logged on the website:
Bug reference: 8293
Logged by: Yaroslav Potapov
Email address: jaroslav.pota...@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: All
Description:
SELECT 'a\b'::json::text
returns text: 'a\b' ,
but it must return 'ab' in my opinion.
I see you didn't get a reply, so let me try. I am no JSON expert, but I
think what is happening is that the system stores a\b because that is
what a JSON/Javascript interpreter would need to understand that value.
It would convert a\b to ab. If we just stored ab, the interpreter
would throw an error on input.
You can see this a little bit using 9.3 beta to pull values based on
keys:
SELECT json_extract_path('{\a: b\c}'::json, 'a');
json_extract_path
---
b\c
(1 row)
SELECT json_extract_path('{\a: b\c}'::json, '\a');
json_extract_path
---
(1 row)
Notice the key is a, not \a.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] 9.3beta2: Failure to pg_upgrade
On Fri, Aug 2, 2013 at 11:20:37PM -0400, Jesse Denardo wrote: Alvaro, I applied the patch and tried upgrading again, and everything seemed to work as expected. We are now up and running the beta! Yeah, great, thanks everyone! -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] 9.3beta2: Failure to pg_upgrade
On Tue, Jul 30, 2013 at 10:17:52AM -0400, Jesse Denardo wrote:
Name: Jesse Denardo
Release: 9.2.2 - 9.3beta2
Test Type: Install/Upgrade Test
Test Detail: pg_upgrade in a fresh install of 9.3beta2
Platform: Debian Linux 6.0.5
Installation Method: From source
Platform Detail: Debian Linux 6.0.5, 2.6.32.45-grsec-2.2.2-r3, x86_64
Test Procedure:
I made a byte for byte copy of our exsting 9.2.2 Postgres directory (which
includes the data directory), changed the port, and started it up. I pointed
I assume you did this while the server was down.
command: /home/postgres/9.3beta2/bin/pg_ctl -w -l pg_upgrade_server.log -D
/home/postgres/9.3beta2/data -o -p 50432 -b -c synchronous_commit=off -c
fsync=off -c full_page_writes=off -c listen_addresses='' -c
unix_socket_permissions=0700 -c unix_socket_directories='/home/postgres'
start
pg_upgrade_server.log 21
waiting for server to startLOG: database system was shut down at
2013-07-30 09:57:58 EDT
FATAL: could not access status of transaction 2983
DETAIL: Could not read from file pg_multixact/offsets/ at offset 8192:
Success.
OK, I actually have an idea on this. Here is the pg_upgrade code:
/*
* If the old server is before the MULTIXACT_FORMATCHANGE_CAT_VER change
* (see pg_upgrade.h) and the new server is after, then we don't copy
* pg_multixact files, but we need to reset pg_control so that the new
* server doesn't attempt to read multis older than the cutoff value.
*/
if (old_cluster.controldata.cat_ver = MULTIXACT_FORMATCHANGE_CAT_VER
new_cluster.controldata.cat_ver = MULTIXACT_FORMATCHANGE_CAT_VER)
{
copy_subdir_files(pg_multixact/offsets);
copy_subdir_files(pg_multixact/members);
prep_status(Setting next multixact ID and offset for new cluster);
/*
* we preserve all files and contents, so we must preserve both next
* counters here and the oldest multi present on system.
*/
exec_prog(UTILITY_LOG_FILE, NULL, true,
\%s/pg_resetxlog\ -O %u -m %u,%u \%s\,
new_cluster.bindir,
old_cluster.controldata.chkpnt_nxtmxoff,
old_cluster.controldata.chkpnt_nxtmulti,
old_cluster.controldata.chkpnt_oldstMulti,
new_cluster.pgdata);
check_ok();
}
and the C comment is:
/*
* pg_multixact format changed in 9.3 commit
0ac5ad5134f2769ccbaefec73844f85,
* (Improve concurrency of foreign key locking) which also updated
catalog
* version to this value. pg_upgrade behavior depends on whether old
and new
* server versions are both newer than this, or only the new one is.
*/
#define MULTIXACT_FORMATCHANGE_CAT_VER 201301231
So, first, this is new in 9.3, and second, it seems the comment we need
to reset pg_control so that the new server doesn't attempt to read
multis older than the cutoff value is not working. Alvaro, can you
comment on this? I think you added this code with this commit:
commit 0ac5ad5134f2769ccbaefec73844f8504c4d6182
Author: Alvaro Herrera alvhe...@alvh.no-ip.org
Date: Wed Jan 23 12:04:59 2013 -0300
...
pg_upgrade also needs to be careful to copy pg_multixact files over from
the old server to the new, or at least part of multixact.c state,
depending on the versions of the old and new servers.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] 9.3beta2: Failure to pg_upgrade
On Wed, Jul 31, 2013 at 12:56:33PM -0400, Alvaro Herrera wrote: Bruce Momjian escribió: On Tue, Jul 30, 2013 at 10:17:52AM -0400, Jesse Denardo wrote: So, first, this is new in 9.3, and second, it seems the comment we need to reset pg_control so that the new server doesn't attempt to read multis older than the cutoff value is not working. Alvaro, can you comment on this? I think you added this code with this commit: So it seems. I will have a look. Well, the good news is that this is new 9.3 code, this bug was caught during beta, and pg_upgrade failed visibly, rather than silently. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7709: psql \copy csv terminates with \. in quoted region
On Tue, Nov 27, 2012 at 12:33:44PM -0500, Tom Lane wrote:
tgarn...@panjiva.com writes:
psql \copy terminates at \. by itself in a line even if the format is csv
and the \. is inside a quoted region. This means that some values can't be
round-tripped by psql \copy. Tested and the native postgresql COPY handles
this case correctly.
Ugh. This seems like a rather fundamental oversight in the CSV feature.
The problem is that psql has no idea whether the copy is being done in
CSV mode or not --- and even if it did, it doesn't parse the data fully
enough to realize whether a \. line is inside quotes or not.
In the case of out-of-line data files, it might be reasonable to just
dispense with the check for \. altogether and always ship the whole file
to the backend; I think there's a \. check on the backend side. (Not
sure this is safe in V2 protocol, but I doubt anyone cares anymore
about that.)
In the case of in-line data in a script file, CSV mode seems a bit
broken in any case; there's no concept of a terminator in CSV, AFAIK.
So maybe we don't have to worry about that.
I have added a C comment documenting this bug; patch attached.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c
new file mode 100644
index b5732c7..c1e7cfe
*** a/src/bin/psql/copy.c
--- b/src/bin/psql/copy.c
*** handleCopyIn(PGconn *conn, FILE *copystr
*** 635,640
--- 635,645
/* check for EOF marker, but not on a partial line */
if (firstload)
{
+ /*
+ * This code erroneously assumes '\.' on a line alone
+ * inside a quoted CSV string terminates the \copy.
+ * http://www.postgresql.org/message-id/e1tdnvq-0001ju...@wrigleys.postgresql.org
+ */
if (strcmp(buf, \\.\n) == 0 ||
strcmp(buf, \\.\r\n) == 0)
{
--
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 #6528: pglesslog still referenced in docs, but no 9.1 support
On Thu, Jan 24, 2013 at 06:45:02PM -0500, Bruce Momjian wrote: On Thu, Jan 24, 2013 at 08:43:37PM -0300, Alvaro Herrera wrote: Bruce Momjian escribió: On Tue, Sep 4, 2012 at 02:01:54PM -0400, Bruce Momjian wrote: On Tue, Sep 4, 2012 at 12:49:40PM -0500, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: On Tue, Sep 4, 2012 at 12:11:53PM -0500, Kevin Grittner wrote: What do you think would be the right thing to do with it at this point? Well, there should probably be a tar files with a README. What about adding this to /contrib? I would be happy to add it to contrib if people want it there. There was some discussion of that when I wrote it, but then there was a feeling that it would be uninteresting once people had streaming replication. I'm skeptical that people will ever lose interest in archiving WAL files, because of the not-uncommon need to keep archival backups or restore to a point in time to recover from a mangled table. As long as those needs are there, I suspect that people will want to compress WAL files, including making partially-filled files smaller. Agreed. Please move forward on the contrib idea. Kevin, did this get accomplished? Just to be clear, what we're proposing adding to contrib is clearxlogtail, not pglesslog, right? I was not clear on exactly what Kevin was going to add. Kevin? Any news on this? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7650: DROP TABLE IF EXISTS fails if schema does not exist
On Sun, Nov 11, 2012 at 03:17:34PM +, j...@zer7.com wrote: The following bug has been logged on the website: Bug reference: 7650 Logged by: James Bellinger Email address: j...@zer7.com PostgreSQL version: 9.1.5 Operating system: Ubuntu Linux 12.04 Server Description: Not sure this worked last time (I got some DMARC error), but here's a bug... If a schema does not exist, DROP TABLE IF EXISTS schema.table will cause the transaction to fail (due to 'schema does not exist'). End result, DROP TABLE IF EXISTS is pretty much unusable with schemas. It needs to act the same as DROP TABLE IF EXISTS does for the default schema. FYI, this will be fixed in Postgres 9.3. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Inconsistency between TO_CHAR() and TO_NUMBER()
On Fri, May 10, 2013 at 11:49:18AM -0400, Tom Lane wrote: Patryk Kordylewski p...@fooby.de writes: SET lc_numeric TO 'de_DE.UTF-8'; SET SELECT TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'), TO_NUMBER(TO_CHAR(123456789.123, 'FM99G999G999G999G999G999G999D000'), 'FM99G999G999G999G999G999G999D000'); to_char | to_number -+--- 123.456.789,123 | 123.456 (1 row) I looked into this, and find that the reason it misbehaves is that NUM_numpart_from_char() will treat a '.' as being a decimal point *without any regard to locale considerations*. So even if we have a locale-dependent format string and a locale that says '.' is a thousands separator, it does the wrong thing. It's a bit surprising nobody's complained of this before. I propose the attached patch. I'm slightly worried though about whether this might break any existing applications that are (incorrectly) depending on a D format specifier being able to match '.' regardless of locale. Perhaps we should only apply this to HEAD and not back-patch? I never trust to_char() changes for backpatching. ;-) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #8107: How to downgrade database from 9.2.3 to 8.4 ?
On Tue, Apr 23, 2013 at 04:18:17PM +, roberto.menon...@netspa.it wrote: The following bug has been logged on the website: Bug reference: 8107 Logged by: Roberto Email address: roberto.menon...@netspa.it PostgreSQL version: 9.2.3 Operating system: CentOS 5.6 (Final) Description: Hy. This is not a bug but a simple request ! Is there a way to downgrade a database from version 9.2.3 to 8.4.x ? I tried a pg_dump from 9.2.3 and a pg_restore from 9.2.3 TO a 8.4.7 server. but I receive several errors. Does it exist another way ? I recommend doing a schema dump, adjusting the output file until it generates no errors, then restore that, and then restore the data. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] JRV problem with pg_upgrade
Please run pg_ctl manually on the failed cluster and get the error message reported, or find out how to make it work successfully. --- On Mon, Mar 25, 2013 at 04:19:48PM +0100, RAVALET Jacques wrote: Hello, I'am a database administrateur and I get trouble to upgrade database postgres from 8.4.6 to 9.2.3. During pg_upgrade, I can't start the old cluster with parameter -c listen_addresses='' This syntax work to start the old cluster and to access to the database /distrib/postgresql/app/chfdba/produit/8.4.6/bin/pg_ctl -w -l pg_upgrade_server.log -D /distrib/postgresql/data/chorus_formulaires/data -o -p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=20 -c listen_addresses='*' -c unix_socket_permissions=0700 start This syntax doesn't work to start the old cluster /distrib/postgresql/app/chfdba/produit/8.4.6/bin/pg_ctl -w -l pg_upgrade_server.log -D /distrib/postgresql/data/chorus_formulaires/data -o -p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=20 -c listen_addresses='' -c unix_socket_permissions=0700 start But pg_upgrade use this bad syntax even I try to force -c listen_addresses= '*' pg_upgrade -c -d /distrib/postgresql/data/chorus_formulaires/data -D /distrib/ postgresql/data/chorus_formulaires/data/9.2.3 -b /distrib/postgresql/app/chfdba /produit/8.4.6/bin -B /distrib/postgresql/app/chfdba/produit/9.2.3/bin -o -c listen_addresses='*' more pg_upgrade_server.log could not connect to old postmaster started with the command: /distrib/postgresql/app/chfdba/produit/8.4.6/bin/pg_ctl -w -l pg_upgrade_server.log -D /distrib/postgresql/data/chorus_formulaires/data -o -p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=20 -c listen_addresses='*' -c listen_addresses='' -c unix_socket_permissions=0700 start Failure, exiting I don't know how to workaround this problem ? Best regards, Jacques ravalet -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7888: Small issue on wiki page Round time
It is a wiki, so you can easily get an account and make the change yourself. Thanks. --- On Sun, Feb 17, 2013 at 07:14:00PM +, s...@spamik.cz wrote: The following bug has been logged on the website: Bug reference: 7888 Logged by: Jan Krajdl Email address: s...@spamik.cz PostgreSQL version: Unsupported/Unknown Operating system: all Description: Hi, I have found small issue on this wiki page: http://wiki.postgresql.org/wiki/Round_time There is mistake in alternative approach function - select '1970-01-01'::timestamptz results in timestamp '1970-01-01 00:00:00' with local timezone. But function is using it as a start of epoch which was same time but in timezone GMT. If you use this function and you have postgres server in non GMT timezone, rounded results are in GMT timezone but they have timezone information about local timezone. I'm quite noob with postgres but I tried to fix it and it looks that this code: CREATE FUNCTION date_round(base_date timestamptz, round_interval INTERVAL) RETURNS timestamptz AS $BODY$ SELECT TIMESTAMP WITH TIME ZONE 'epoch' + (EXTRACT(epoch FROM $1)::INTEGER + EXTRACT(epoch FROM $2)::INTEGER / 2) / EXTRACT(epoch FROM $2)::INTEGER * EXTRACT(epoch FROM $2)::INTEGER * INTERVAL '1 second'; $BODY$ LANGUAGE SQL STABLE; is doing it better - returned time looks correct. So if you want you can update this page. Regards, -- Jan Krajdl -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7873: pg_restore --clean tries to drop tables that don't exist
On Wed, Feb 13, 2013 at 08:22:43PM +, auta...@urth.org wrote: The following bug has been logged on the website: Bug reference: 7873 Logged by: Dave Rolsky Email address: auta...@urth.org PostgreSQL version: 9.2.3 Operating system: Linux Description: When you pass the --clean option to pg_restore it tries to drop tables without checking if they exist. This results in lots of error output. If you're running pg_restore via an automated process it's very hard to distinguish between these ok errors and real errors. It should be using DROP TABLE IF EXISTS and the equivalent for constraints. Well, I think the question is whether you want error feedback for things that don't exist. I don't really know the answer. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7874: GUC's not in database dumps
On Thu, Feb 14, 2013 at 01:37:04AM +, j...@tanga.com wrote: The following bug has been logged on the website: Bug reference: 7874 Logged by: Joe Van Dyk Email address: j...@tanga.com PostgreSQL version: 9.2.1 Operating system: OSX, Linux Description: If I run: alter database foo set my.name = 'joe'; that GUC isn't available inside backups of that database. It does show up when doing pg_dumpall -s. It does *not* show up when doing pg_dumpall -g This is a TODO: https://wiki.postgresql.org/wiki/Todo#ALTER Refactor handling of database attributes between pg_dump and pg_dumpall Currently only pg_dumpall emits database attributes, such as ALTER DATABASE SET commands and database-level GRANTs. Many people wish that pg_dump would do that. One proposal is to let pg_dump issue such commands if the -C switch was used, but it's unclear whether that will satisfy the demand. http://www.postgresql.org/message-id/4864f001.50...@archonet.com http://www.postgresql.org/message-id/11646.1272814...@sss.pgh.pa.us -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7885: postmaster panic on startup does not release shared memory
When we panic, we PANIC, meaning we don't jump around looking for cleanup stuff, which might make things worse. Postgres should never panic, so it would be good if you found the cause of the panic. Does restarting the server remove the old shared memory stuff? --- On Fri, Feb 15, 2013 at 06:33:21PM +, david.tho...@enterprisedb.com wrote: The following bug has been logged on the website: Bug reference: 7885 Logged by: David Thomas Email address: david.tho...@enterprisedb.com PostgreSQL version: 9.2.3 Operating system: CentOS 6.3 Description: It seems that if the postmaster encounters a PANIC condition during startup, it leaves it's allocated shared memory segments around: -bash-4.1$ ipcs -a -- Shared Memory Segments keyshmid owner perms bytes nattch status -- Semaphore Arrays keysemid owner perms nsems -- Message Queues keymsqid owner perms used-bytes messages -bash-4.1$ /usr/pgsql-9.2/bin/postmaster --single -D /var/lib/pgsql/9.2/data/ test PANIC: could not locate a valid checkpoint record Aborted -bash-4.1$ ipcs -a -- Shared Memory Segments keyshmid owner perms bytes nattch status 0x0001 753664 postgres 60041279488 0 -- Semaphore Arrays keysemid owner perms nsems 0x0001 5439490postgres 60017 0x0002 5472259postgres 60017 0x0003 5505028postgres 60017 0x0004 5537797postgres 60017 0x0005 5570566postgres 60017 0x0006 5603335postgres 60017 0x0007 5636104postgres 60017 -- Message Queues keymsqid owner perms used-bytes messages Considering that it was able to allocate this memory before the panic occurred, it should remove them before exiting. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7886: date_trunc(date) returning timestamptz instead of timestamp
On Fri, Feb 15, 2013 at 09:27:40PM +, nick.bax...@gmail.com wrote:
The following bug has been logged on the website:
Bug reference: 7886
Logged by: Nick Baxter
Email address: nick.bax...@gmail.com
PostgreSQL version: 9.0.3
Operating system: Linux 2.6.18
Description:
9.9.2. indicates that date_trunc can be called with a date value (which will
be cast to a timestamp). And regardless of the input, that the result will
be of type timestamp. When I call it with a date, I get a timestamp with
time zone instead, as indicated by the psql output.
# select date_trunc('month',date '2013-2-15');
date_trunc
2013-02-01 00:00:00-06
(1 row)
That documentation often uses timestamp when it means timestamp with
time zone. Not sure why that is.
\df shows the supported functions:
test= \df date_trunc
List of functions
Schema |Name| Result data type |
Argument data types| Type
++-+---+
pg_catalog | date_trunc | interval| text, interval
| normal
pg_catalog | date_trunc | timestamp without time zone | text,
timestamp without time zone | normal
pg_catalog | date_trunc | timestamp with time zone| text,
timestamp with time zone| normal
This returns a timestamp without time zone:
test= select date_trunc('month',timestamp '2013-2-15');
date_trunc
-
2013-02-01 00:00:00
(1 row)
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #7873: pg_restore --clean tries to drop tables that don't exist
On Fri, Feb 15, 2013 at 04:06:12PM -0600, Dave Rolsky wrote: On Fri, 15 Feb 2013, Bruce Momjian wrote: On Wed, Feb 13, 2013 at 08:22:43PM +, auta...@urth.org wrote: The following bug has been logged on the website: Bug reference: 7873 Logged by: Dave Rolsky Email address: auta...@urth.org PostgreSQL version: 9.2.3 Operating system: Linux Description: When you pass the --clean option to pg_restore it tries to drop tables without checking if they exist. This results in lots of error output. If you're running pg_restore via an automated process it's very hard to distinguish between these ok errors and real errors. It should be using DROP TABLE IF EXISTS and the equivalent for constraints. Well, I think the question is whether you want error feedback for things that don't exist. I don't really know the answer. Fair enough. It should probably an option to add if exists, at least. I can't imagine I'm the only using this tool to ship database updates around to different machines, some of which may not have new tables. I'd really like to be able to know when the restore fails versus when it succeeds but is noisy. All I can say is I don't remember anyone asking for this in the past. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7884: pg_upgradecluster is terribly slow
On Fri, Feb 15, 2013 at 03:55:02PM -0800, John R Pierce wrote: On 2/15/2013 10:27 AM, Kevin Grittner wrote: You might want to try the utility which *is* supported by the PostgreSQL community, which is pg_upgrade. For speed, consider using the -k option. I've been able to upgrade a 3 TB database in just a few minutes with that. (Timing depends more on the number of database objects than their size.) do note this -k option only functions if the old and new cluster are on the same mount point, so the files can be mv'd Actually, hard linked, not moved. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7852: TeamPostgresql is giving error
On Tue, Feb 5, 2013 at 06:30:56PM +0100, Magnus Hagander wrote: On Feb 5, 2013 6:20 PM, yogesh.khandel...@emc.com wrote: The following bug has been logged on the website: Bug reference: 7852 Logged by: Yogesh Khandelwal Email address: yogesh.khandel...@emc.com PostgreSQL version: 9.0.8 Operating system: Linux Description: hi, I've deployed TeamPostgreSQL on my server and trying to access my database using that and getting the following error while trying to access the schema/tables Team PostgreSQL is a third party product, and you need to bring this up with their support, since it's not an issue in postgresql. FYI, TeamPostgreSQL has this to the bottom of their web pages to avoid confusion: TeamPostgreSQL is a project of Webworks (webworks.dk), and is not affiliated with the PostgreSQL Project. The PostgreSQL trademark is used with permission. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] [BUGS] BUG #6572: The example of SPI_execute is bogus
On Wed, Aug 29, 2012 at 01:13:51PM +, Rajeev rastogi wrote: From: pgsql-bugs-ow...@postgresql.org [pgsql-bugs-ow...@postgresql.org] on behalf of Bruce Momjian [br...@momjian.us] Sent: Wednesday, August 29, 2012 8:46 AM To: Tom Lane Cc: Robert Haas; Hitoshi Harada; pgsql-bugs@postgresql.org; pgsql-hack...@postgresql.org Subject: Re: [HACKERS] [BUGS] BUG #6572: The example of SPI_execute is bogus On Sun, Apr 15, 2012 at 12:29:39PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Apr 5, 2012 at 2:39 AM, Hitoshi Harada umi.tan...@gmail.com wrote: On Wed, Apr 4, 2012 at 8:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: Given the lack of complaints since 9.0, maybe we should not fix this but just redefine the new behavior as being correct? But it seems mighty inconsistent that the tuple limit would apply if you have RETURNING but not when you don't. In any case, the ramifications are wider than one example in the SPI docs. To be honest, I was surprised when I found tcount parameter is said to be applied to even INSERT. I believe people think that parameter is to limit memory consumption when returning tuples thus it'd be applied for only SELECT or DML with RETURNING. So I'm +1 for non-fix but redefine the behavior. Who wants to limit the number of rows processed inside the backend, from SPI? Yeah. Okay, apparently nobody cares about RETURNING behaving differently from non-RETURNING, so the consensus is to redefine the current behavior as correct. That means what we need is to go through the docs and see what places need to be updated (and, I guess, back-patch the changes to 9.0). I will get to this if nobody else does, but not right away. Would someone make the doc change outlined above? Thanks. I would like to work on this documentation bug. As per analysis I am planning to update following SPI function: 1. SPI_Execute: Here we will mention that argument count is used only for the kind of command which returns result i.e. all kind of SELECT and DML with returning clause. count is ignored for any other kind of commands. I will add one example also to indicate the difference. 2. SPI_execute_plan_with_paramlist: Here we can give just reference to SPI_execute i.e. I will mention that count has same interpretation as in SPI_execute. 3. SPI_execp: Here we can give just reference to SPI_execute i.e. I will mention that count has same interpretation as in SPI_execute. Would someone please provide answers to these questions, or write a patch? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] [BUGS] BUG #6572: The example of SPI_execute is bogus
On Thu, Jan 24, 2013 at 04:51:04PM -0500, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Would someone make the doc change outlined above? Thanks. Sorry, I'd nearly forgotten about this issue. Will see about fixing the docs. (It looks like some of the comments in execMain.c could use work too.) Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6528: pglesslog still referenced in docs, but no 9.1 support
On Tue, Sep 4, 2012 at 02:01:54PM -0400, Bruce Momjian wrote: On Tue, Sep 4, 2012 at 12:49:40PM -0500, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: On Tue, Sep 4, 2012 at 12:11:53PM -0500, Kevin Grittner wrote: What do you think would be the right thing to do with it at this point? Well, there should probably be a tar files with a README. What about adding this to /contrib? I would be happy to add it to contrib if people want it there. There was some discussion of that when I wrote it, but then there was a feeling that it would be uninteresting once people had streaming replication. I'm skeptical that people will ever lose interest in archiving WAL files, because of the not-uncommon need to keep archival backups or restore to a point in time to recover from a mangled table. As long as those needs are there, I suspect that people will want to compress WAL files, including making partially-filled files smaller. Agreed. Please move forward on the contrib idea. Kevin, did this get accomplished? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6528: pglesslog still referenced in docs, but no 9.1 support
On Thu, Jan 24, 2013 at 08:43:37PM -0300, Alvaro Herrera wrote: Bruce Momjian escribió: On Tue, Sep 4, 2012 at 02:01:54PM -0400, Bruce Momjian wrote: On Tue, Sep 4, 2012 at 12:49:40PM -0500, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: On Tue, Sep 4, 2012 at 12:11:53PM -0500, Kevin Grittner wrote: What do you think would be the right thing to do with it at this point? Well, there should probably be a tar files with a README. What about adding this to /contrib? I would be happy to add it to contrib if people want it there. There was some discussion of that when I wrote it, but then there was a feeling that it would be uninteresting once people had streaming replication. I'm skeptical that people will ever lose interest in archiving WAL files, because of the not-uncommon need to keep archival backups or restore to a point in time to recover from a mangled table. As long as those needs are there, I suspect that people will want to compress WAL files, including making partially-filled files smaller. Agreed. Please move forward on the contrib idea. Kevin, did this get accomplished? Just to be clear, what we're proposing adding to contrib is clearxlogtail, not pglesslog, right? I was not clear on exactly what Kevin was going to add. Kevin? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve
On Sat, Jan 19, 2013 at 10:45:15PM +0400, George Machitidze wrote: Hi Bruce, Tom The backstory on this is at the cited Red Hat bug ... apparently the OP decided I was clueless and he needed to consult some real authorities. Oh come on, I'm very sure you both are good guys and know what you are doing, none of us is ignorant bastard :) Decided to open case here too, because of simple reason - maybe someone had same issue, or knows how pg_upgrade works (in details) better than me, because I am clueless. This is test DB and I can erase it, but I'm very sure there's something wrong in upgrade process - this is what I want to be solved. Now, we can open a bottle of whiskey and go back to the problem: 1. I didn't run postmaster before/during pg_upgrade, it was never invoked manually in this process 2. There is no pid file AFTER application is stopped, but looks like it's there while pg_upgrade is running - strace showed that and there is no need to run FAM to verify that I don't know how pg_upgrade works, looks like it's trying to start postmaster, which runs, postmaster.pid is created, then postmaster fails stop or needs some more time bedore pg_upgrade is checking it's pid. That's what I see. So, is pg_upgrade starting postmaster? If yes, then when (at which step) and why pid file check is done. That's all what we all want to know, right? The pid check is done before pg_upgrade starts or stops any postmaster, to make sure both servers are down before it starts. Tom wants that testing improved. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve
On Sat, Jan 19, 2013 at 11:27:28AM -0500, Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
Why is a clean shutdown important? If the server crashed, we would have
committed transactions in the WAL files which are not transfered to the
new server, and would be lost.
I am hesistant to even start such an old server because pg_upgrade never
modifies the old server. Even starting it in that case would be
modifying it.
I'm not really following this logic. If the old cluster was in a
crashed state, why would we not expect that starting a postmaster would
be the best (only) way to repair the damage and make everything good
again? Isn't that exactly what the user would have to do anyway? What
other action would you expect him to take instead?
(But, at least with the type of packaging I'm using in Fedora, he would
first have to go through a package downgrade/reinstallation process,
because the packaging provides no simple scripted way of manually
starting the old postgres executable, only the new one. Moreover, what
pg_upgrade is printing provides no help in figuring out whether that's
the next step.)
I do sympathize with taking a paranoid attitude here, but I'm failing
to see what advantage there is in not attempting to start the old
postmaster. In the *only* case that pg_upgrade is successfully
protecting against with this logic, namely there's-an-active-postmaster-
already, the postmaster is equally able to protect itself. In other
cases it would be more helpful not less to let the postmaster analyze
the situation.
The other problem is that if the server start fails, how do we know if
the failure was due to a running postmaster?
Because we read the postmaster's log file, or at least tell the user to
do so. That report would be unambiguous, unlike pg_upgrade's.
Attached is a WIP patch to give you an idea of how I am going to solve
this problem. This comment says it all:
! /*
!* If we have a postmaster.pid file, try to start the server. If
!* it starts, the pid file was stale, so stop the server. If it
!* doesn't start, assume the server is running.
!*/
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 1780788..25cf23c
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*** check_and_dump_old_cluster(bool live_che
*** 78,84
/* -- OLD -- */
if (!live_check)
! start_postmaster(old_cluster);
set_locale_and_encoding(old_cluster);
--- 78,84
/* -- OLD -- */
if (!live_check)
! start_postmaster(old_cluster, true);
set_locale_and_encoding(old_cluster);
*** issue_warnings(char *sequence_script_fil
*** 201,207
/* old = PG 8.3 warnings? */
if (GET_MAJOR_VERSION(old_cluster.major_version) = 803)
{
! start_postmaster(new_cluster);
/* restore proper sequence values using file created from old server */
if (sequence_script_file_name)
--- 201,207
/* old = PG 8.3 warnings? */
if (GET_MAJOR_VERSION(old_cluster.major_version) = 803)
{
! start_postmaster(new_cluster, true);
/* restore proper sequence values using file created from old server */
if (sequence_script_file_name)
*** issue_warnings(char *sequence_script_fil
*** 224,230
/* Create dummy large object permissions for old PG 9.0? */
if (GET_MAJOR_VERSION(old_cluster.major_version) = 804)
{
! start_postmaster(new_cluster);
new_9_0_populate_pg_largeobject_metadata(new_cluster, false);
stop_postmaster(false);
}
--- 224,230
/* Create dummy large object permissions for old PG 9.0? */
if (GET_MAJOR_VERSION(old_cluster.major_version) = 804)
{
! start_postmaster(new_cluster, true);
new_9_0_populate_pg_largeobject_metadata(new_cluster, false);
stop_postmaster(false);
}
diff --git a/contrib/pg_upgrade/exec.c b/contrib/pg_upgrade/exec.c
new file mode 100644
index e326a10..2b3c203
*** a/contrib/pg_upgrade/exec.c
--- b/contrib/pg_upgrade/exec.c
*** exec_prog(const char *log_file, const ch
*** 99,104
--- 99,106
fclose(log);
result = system(cmd);
+ if (result != -1)
+ result = WEXITSTATUS(result);
umask(old_umask);
diff --git a/contrib/pg_upgrade/pg_upgrade.c b/contrib/pg_upgrade/pg_upgrade.c
new file mode 100644
index 85997e5..e329cb5
*** a/contrib/pg_upgrade/pg_upgrade.c
--- b/contrib/pg_upgrade/pg_upgrade.c
*** main(int argc, char **argv)
*** 95,101
/* -- NEW -- */
! start_postmaster(new_cluster);
check_new_cluster();
report_clusters_compatible();
--- 95,101
/* -- NEW -- */
! start_postmaster(new_cluster, true);
check_new_cluster
Re: [BUGS] BUG #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve
On Fri, Jan 18, 2013 at 10:19:48PM +, gio...@gmail.com wrote: The following bug has been logged on the website: Bug reference: 7815 Logged by: George Machitidze Email address: gio...@gmail.com PostgreSQL version: 9.2.2 Operating system: Fedora 18 Linux Description: https://bugzilla.redhat.com/show_bug.cgi?id=896161 Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails with invalid message There seems to be a postmaster servicing the old cluster. Looks like pg_upgrade is checking pid file too early without waiting for master process to exit: open(/var/lib/pgsql/data-old/postmaster.pid, O_RDONLY) = 5 How are you shutting down the postmaster? Are you use pg_ctl -w stop? If not, you have to wait for the server to actually shut down before starting pg_upgrade. pg_upgrade is not going to do that waiting. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7815: Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails - invalid status retrieve
On Sat, Jan 19, 2013 at 12:02:31AM -0500, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Fri, Jan 18, 2013 at 10:19:48PM +, gio...@gmail.com wrote: https://bugzilla.redhat.com/show_bug.cgi?id=896161 Upgrading PostgreSQL from 9.1 to 9.2 with pg_upgrade/postgreql-setup fails with invalid message There seems to be a postmaster servicing the old cluster. Looks like pg_upgrade is checking pid file too early without waiting for master process to exit: open(/var/lib/pgsql/data-old/postmaster.pid, O_RDONLY) = 5 How are you shutting down the postmaster? Are you use pg_ctl -w stop? If not, you have to wait for the server to actually shut down before starting pg_upgrade. pg_upgrade is not going to do that waiting. The backstory on this is at the cited Red Hat bug ... apparently the OP decided I was clueless and he needed to consult some real authorities. Yes, it was clear there was some backstory in reading that thread. The existing pg_control clearly says that the cluster was shut down, so it's not clear why there's still a postmaster.pid file there. There's some debugging to be done yet about how that got to be that way. (AFAICS the RPM upgrade process ought to shut down the old postmaster before installing a new one; but somehow that went wrong, or else a doppelganger postmaster.pid rose from the dead. Anyway, that's not a matter for this list because it involves Red Hat upgrade processes, not anything supplied by the community.) In the meantime, I was wondering a bit why pg_upgrade looks at the postmaster.pid file at all. Generally we recommend that startup scripts *not* look at the lock file but just try to start a postmaster, and leave it to the postmaster to decide if there's a valid lockfile present. Is it really appropriate for pg_upgrade to do this differently? I think the complained-of case would have gone through cleanly if that error check weren't there, or in any case the postmaster would have done a better job of checking for a conflicting postmaster. The reason we check for postmaster.pid is so we can give the user a clue about which postmaster is running. We want to make sure everything is super-clean before we do anything. What we could do is to first try to start each cluster, and then fail if the start fails, but the start could fail for all sorts of reasons so it doesn't really seems like a win. Also, we don't want to start on a non-clean shutdown, so the missing pid file tells us it was clean. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7750: pid file conflict in RedHat
On Mon, Dec 10, 2012 at 06:41:29PM +, postg...@tbruce.com wrote: The following bug has been logged on the website: Bug reference: 7750 Logged by: Timothy J Bruce Email address: postg...@tbruce.com PostgreSQL version: 9.1.6 Operating system: RedHat v6.2 Description: In line 238 of the rpm based init.d script, the pid file is called specifically with a qualified path (status -p /var/run/postmaster-9.1.pid), even though line 70 specifies a variable to point to the pid file (pidfile=/var/run/postmaster-9.1.pid). This is with rpm version of postgres 9.1.4 and 9.1.6 (we haven't tested 9.1.7 at this time). I think you need to report this to Red Hat; we didn't create that file. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7750: pid file conflict in RedHat
On Tue, Dec 11, 2012 at 12:28:40PM -0500, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Mon, Dec 10, 2012 at 06:41:29PM +, postg...@tbruce.com wrote: In line 238 of the rpm based init.d script, the pid file is called specifically with a qualified path (status -p /var/run/postmaster-9.1.pid), even though line 70 specifies a variable to point to the pid file (pidfile=/var/run/postmaster-9.1.pid). This is with rpm version of postgres 9.1.4 and 9.1.6 (we haven't tested 9.1.7 at this time). I think you need to report this to Red Hat; we didn't create that file. Red Hat didn't either. This report is evidently about the PGDG RPMs. Yes, I have found it is a PGDG RPM and forwarded it to Devrim. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7722: extract(epoch from age(...)) appears to be broken
On Mon, Dec 3, 2012 at 03:05:57AM -0300, Alvaro Herrera wrote:
aanisi...@inbox.ru wrote:
The following bug has been logged on the website:
Bug reference: 7722
Logged by: Artem Anisimov
Email address: aanisi...@inbox.ru
PostgreSQL version: 9.2.1
Operating system: Slackware Linux 14.0/amd64
Description:
The following to queries give the same result (first arguments to age()
differ in the day number only, second arguments are identical):
select extract(epoch from age('2012-11-23 16:41:31', '2012-10-23
15:56:10'));
and
select extract(epoch from age('2012-11-22 16:41:31', '2012-10-23
15:56:10'));
alvherre=# select age('2012-11-22 16:41:31', '2012-10-23 15:56:10');
age
--
30 days 00:45:21
(1 fila)
alvherre=# select age('2012-11-23 16:41:31', '2012-10-23 15:56:10');
age
1 mon 00:45:21
(1 fila)
The problem is that age() returns 30 days in one case, and one month in the
other; extract() then considers the month as equivalent to 30 days. This is
documented as such, see [1].
[1] http://www.postgresql.org/docs/current/static/functions-datetime.html
Wow, that is a weird case. In the first test, we count the number of
days because it is less than a full month. In the second case, we call
it a full month, but then forget how long it is. Not sure how we could
improve this.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #7706: Can slave database supports creating temporary tables?
On Mon, Nov 26, 2012 at 01:59:44PM +, wil...@leyou.com wrote: The following bug has been logged on the website: Bug reference: 7706 Logged by: wilson Email address: wil...@leyou.com PostgreSQL version: 9.2.1 Operating system: debian Description: Hi Dears, In M/S mode,Slave database can't create temporary tables right now,just support read only access.But sometimes,for support some complex queries,we need create some temp tables actually. So I suggest postgresql slave node can support create temp tables in the next version,that'll be great!!! Well, because the slave is read-only, we have not figured out how to enable creation of temporary tables on the slave. It isn't even on our TODO list because we have no idea how we would do it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7683: pg_upgrade missing configuration file
On Tue, Nov 20, 2012 at 11:23:13AM +, bernhard.schra...@innogames.de wrote: To point this out, postgresql 9.0 and 9.2 are not running, if 9.0 is running, the 9.0 tests succeed, but afaik they shouldn't running, or at least 9.2 shouldn't running during upgrade, but without the link to the config it will never start. The same for 9.0, if it is shut down and the link is present, it works. I assume this is a bug or at least a missing option for pg_upgrade in addition to debian. Do you confirm? Or do i undestand something completly wrong? I hope not, thanks in advance Have you seen this in the pg_upgrade 9.2 manual? If you are upgrading a pre-productnamePostgreSQL/ 9.2 cluster that uses a configuration-file-only directory, you must pass the real data directory location to applicationpg_upgrade/, and pass the configuration directory location to the server, e.g. literal-d /real-data-directory -o '-D /configuration-directory'/. -o, --old-options=OPTIONS old cluster options to pass to the server -O, --new-options=OPTIONS new cluster options to pass to the server -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [GENERAL] fuzzystrmatch module buggy? observations
On Tue, Oct 30, 2012 at 02:29:09PM +0100, r d wrote:
The fuzzystrmatch module (http://www.postgresql.org/docs/9.2/static/
fuzzystrmatch.html) is currently, as of 9.2.1, documented with the caution At
present, the soundex, metaphone, dmetaphone, and dmetaphone_alt functions do
not work well with multibyte encodings (such as UTF-8).
While the venerable algorithms contained in the module seem to generally work
for Latin strings from European languages which all have accented/diacritic
characters such as äöüñáéíóúàèìòù, for languages with non-Latin characters
such
as Kyrillic, Hebrew, Arabic, Chinese, these venerable algorithms return NULL
(empty) or plain weirdness.
Some examples:
dmetaphone ('Новости') = 'NN'
soundex ('Новости') = NULL
dmetaphone ('לפחות') = NULL
soundex ('לפחות') = NULL
soundex ('相关搜索') = NULL
dmetaphone ('相关搜索') = NULL
metaphone() crashes with SQL state: 42883 for all these strings (it tells me I
should cast the 'unknown' input).
The string 'äöüñáéíóúàèìòù' causes metaphone(), dmetaphone(), dmetaphone_alt,
soundex() to fail.
Only levenshtein() appears to function correctly with all above inputs, even
when I let it compare Hebrew against Chinese strings.
Summarizing my experience:
* for english (ASCII equivalent), the module works,
* for the rest of the Latin charsets (equivalent to ISO 8859-x) the module
works unreliably,
* for non-latin chars (UTF8 with 2-4 bytes per char) the module does not work
Note: My DB and the OS are set up for UTF-8.
This would appear to be less a problem of Postgresql and the fuzzystrmach
module itself but because there
appear to exist no replacement algorithms adequate for a multilingual world -
at least that is my impression
after looking at the IPA and http://www.lt-world.org websites and branching
out
from there.
This is a very good summary. I was not aware of all these behaviors.
Given all this I have no idea of this is a bug at all or the state-of-the-art
around this topic is inadequate.
I have no idea either.
Questions (to the developers):
- Is there anything in work or planned for the fuzzystrmatch module?
- Does anybody know about adequate replacements or upgrades of the soundex,
metaphone etc. algorithms from academia?
I have not heard of anyone working in this area. What usually happens
is some expert in the field shows up and submits a patch to improve it.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] pg_ctl restart issue with relative paths
On Sun, Oct 14, 2012 at 09:56:46AM -0700, Josh Kupershmidt wrote: On Sat, Oct 13, 2012 at 3:56 PM, Thom Brown t...@linux.com wrote: I have noticed that, using pg_ctl, if you start Postgres using a relative path, then attempt to restart it from anywhere else, it fails. Yeah, I was complaining about the same problem here: http://archives.postgresql.org/pgsql-bugs/2011-10/msg00210.php and at least there's a doc note now warning against using relative paths, though it sure would be nice if we could fix this gripe. FYI, the doc patch is mentioned here: http://archives.postgresql.org/pgsql-bugs/2012-08/msg00133.php -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7573: data loss in corner case using delete_old_cluster.sh (pg_upgrade)
On Thu, Oct 4, 2012 at 10:40:19AM +1000, Maxim Boguk wrote: Test database have a bit unusual tablespace layout: main tablespace partition was mounted inside data directory of the old cluster... E.g.: data directory - /var/lib/postgresql/9.2/main main tablespace (another partition mount point) - /var/lib/postgresql/9.2/main/largedb Can you show us the data directory path of the old and new clusters? --old-datadir=/var/lib/postgresql/9.0/main --new-datadir=/var/lib/postgresql/9.2/main second partition used as tablespace were mounted as: /var/lib/postgresql/9.0/main/largedb pg_upgrade really doesn't know what is inside that old cluster, so it just deletes everything under the data directory. Hmm... may be good idea to try opposite way: default directories and files layout in PostgreSQL data directory well documented and almost never changes. May be instead of rm -rf whole data directory try rm -rf only files and directories which sure belong to the PostgreSQL? Something along with: 1)rm -rf base global pg_clog pg_multixact ... and so on 2)produce warning if any unusual files left in data directory after that (but not delete them). 3)delete data directory itself only if that directory completely empty after step 1 and 2 PS: I know that solution will be not completely error-prone but it will prevent most probably data-loss scenarios. So it's better then nothing. PS: I also think deleting postgresql.conf and pg_hba.conf from old data directory is wrong move too... if admin forget copy pg_hba.conf to the new cluster - these settings could be lost forever after delete_old_cluster.sh . This all seems like a step backwards and adds complexity that will fail. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [DOCS] [BUGS] BUG #7543: Invalid table alias: DELETE FROM table *
On Sun, Sep 16, 2012 at 11:58:06PM -0400, Tom Lane wrote: barrybr...@sierracollege.edu writes: I sometime see my users delete all rows from a table using a command like this: DELETE FROM customer *; The question is: what is the star? Is it a table alias or an output_expression? Neither; it specifies to search the table and its inheritance children, ie, the opposite of ONLY. This has been the default behavior (unless you change the setting of sql_inheritance) for many years, so * has largely fallen into disuse; but it's still accepted. However ... I went looking for documentation on this point, and I'm darned if I can find any. There certainly used to be some, but apparently somebody got over-eager about editing the docs to reflect the modern default behavior. The * doesn't even appear in the syntax summaries for most of the commands where it's allowed, which is flat wrong --- anywhere you can write ONLY tablename, it's valid to write tablename* instead. So we have some docs work to do. Thanks for pointing it out. Is there any value to having * vs just not using ONLY? I am not sure documenting this is helping us, and it would add more clutter. Isn't this like how we don't document the old COPY syntax. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7549: max_connections check should query master when starting standby
On Tue, Sep 18, 2012 at 09:09:13PM +0900, Fujii Masao wrote: On Tue, Sep 18, 2012 at 3:48 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 18.09.2012 09:46, Craig Ringer wrote: On 09/18/2012 07:57 AM, Fujii Masao wrote: If you change the max_connections on the master, you need to take a fresh backup from the master and start the standby from it. WTF, really? No. It's enough to bump up max_connections on the standby, and restart. Hmm... Petteri wants to solve the issue without changing anything on the standby, according to his original post. So in his case, a fresh backup is required. Of course my previous answer was confusing... I assume you mean the slave needs an updated copy of the master's config file changes, not an entire copy of the data directory. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6528: pglesslog still referenced in docs, but no 9.1 support
On Tue, Sep 4, 2012 at 12:11:53PM -0500, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: On Mon, Apr 9, 2012 at 02:07:43PM -0500, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: On Mon, Apr 09, 2012 at 03:37:09PM -0300, Alvaro Herrera wrote: (Another related tool is clearxlogtail which zeroes areas from WAL files when they are empty because of an early switch due to archive timeout). Should we document that? Our shop has been using that since before pglesslog existed, and it has continued to work across many major releases with no change to source code because it doesn't get down to the level of looking at the xlog records themselves, just the segment and page-level structures. I should probably put packaging that up as a proper extension and posting it to PGXN as another thing on the list of things I'll do when I stumble over that pot of round tuits at the end of the rainbow. Right now the source is on pgfoundry. If anybody wants to do anything with it before I find time, feel free. I was going to add a mention of this to our docs, but it seems there are no released files for the project: There is a single 175 line C source file to generate a stand-alone executable filter. The source has not needed any changes since it was finished over 4 years ago. Besides that C file, there is only a make file. I wasn't sure what form of packaging would be easier than just checking out the source from the SCM. What do you think would be the right thing to do with it at this point? Well, there should probably be a tar files with a README. What about adding this to /contrib? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6528: pglesslog still referenced in docs, but no 9.1 support
On Tue, Sep 4, 2012 at 12:49:40PM -0500, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: On Tue, Sep 4, 2012 at 12:11:53PM -0500, Kevin Grittner wrote: What do you think would be the right thing to do with it at this point? Well, there should probably be a tar files with a README. What about adding this to /contrib? I would be happy to add it to contrib if people want it there. There was some discussion of that when I wrote it, but then there was a feeling that it would be uninteresting once people had streaming replication. I'm skeptical that people will ever lose interest in archiving WAL files, because of the not-uncommon need to keep archival backups or restore to a point in time to recover from a mangled table. As long as those needs are there, I suspect that people will want to compress WAL files, including making partially-filled files smaller. Agreed. Please move forward on the contrib idea. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7507: pg_restore silently fails when restoring a db with the --create flag and no user.
On Mon, Aug 27, 2012 at 03:34:10PM +, i...@fuelforce.com wrote: The following bug has been logged on the website: Bug reference: 7507 Logged by: Ian Nobile Email address: i...@fuelforce.com PostgreSQL version: 9.1.4 Operating system: Mac OS X Version 10.8 Description: Attempting to restore a db using pg_restore to a new database server using the --create and --dbname flags fails silently if the db owner username does not exist in the new db or the -O flag is not used. Command used: pg_restore --create --verbose --dbname=testserver /Volumes/USBLINKS1/hostname.testserver.net_postgres_database Well, I tried this and it showed a proper error: $ pg_restore --create --verbose -U yy --dbname=testserver /rtmp/xx pg_restore: connecting to database for restore pg_restore: [archiver (db)] connection to database testserver failed: FATAL: role yy does not exist -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7482: lack of quotation marks in pg_env script
On Tue, Aug 7, 2012 at 12:15:14PM +, m.skrzypkow...@o2.pl wrote: The following bug has been logged on the website: Bug reference: 7482 Logged by: Maciej Skrzypkowski Email address: m.skrzypkow...@o2.pl PostgreSQL version: 9.1.4 Operating system: Windows XP Description: In main application directory, inside pg_env.bat script there is lack of quotation marks for PGDATA and PGLOCALEDIR variables. Dave, has this been addressed? The community does not control pg_env.bat. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7511: 9.2: pg_stat_activity.procpid renamed unnecessarily
On Thu, Aug 30, 2012 at 04:34:18AM +, i...@avdd.co wrote: The following bug has been logged on the website: Bug reference: 7511 Logged by: Adrian Dries Email address: i...@avdd.co PostgreSQL version: Unsupported/Unknown Operating system: any Description: In trying 9.2 I find pg_stat_activity.procpid has been renamed to pid. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=4f42b546fd87a80be30c53a0f2c897acb826ad52 It seems the premise of this is If we're going to rename current_query to query--what Scott's patch does here--that will force all code using pg_stat_activity to be rewritten. This seems like the perfect time to also change procpid to pid, finally blow away that wart. A premise that is untrue: changing a subset of columns on a relation does not force all code using that relation to be rewritten: SELECT procpid FROM pg_stat_activity WHERE procpid pg_backend_pid() True. We were thinking more of tools that display all pg_stat_activity columns. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Upgrading vldb from 8.4.3 to 9.1.4
On Thu, Aug 30, 2012 at 02:48:51PM -0400, Freddie Burgess wrote: We have successfully upgraded Postgresql from 8.4.3 to 9.1.4 in our environment, but we have some questions/issues. Before the upgrade: Postgres8 data location -- /usr/local/pgsql/data -- /u01/fiber/postgres843/postgres_data Postgres9 data location -- /opt/PostgreSQL/9.1/data -- /u01/fiber/postgres_data After the upgrade: Postgres9 (userdb schema) data location -- /usr/local/pgsql/data -- /u01/fiber/postgres843/postgres_data Postgres9 data location -- /opt/PostgreSQL/9.1/data -- /u01/fiber/postgres_data We successfully moved all of the data files to /u01/fiber/postgres_data in an effort to have all of the files located in the same area, but we are currently stuck with regard to the two high level data location points: /usr/local/pgsql/data and /opt/PostgreSQL/9.1/data, which are both soft links to the fiber channel. Is there any way in Postgres to re-define the tablespace locations, or is the only option available to us is to create new tablespaces w/the correct directory paths and then move all of the objects (i.e. tables, indexes, etc.) to the new tablespaces? This would take some time to script due to the amount of objects we have, but it is doable. Are there any other alternatives? How does Postgres moves the objects? Does It makes a copy of the object in the new location and then deletes the original. If we Multiply by 5.4 TB of data files, are we looking at an estimated completion time on the same order of magnitude as the time it takes for us to perform the backup itself, which takes approximately 5-7 days? Finally, when is it safe to remove the 8.4.3 objects/directory? You can move the data directory containing the default tablespace by just shutting down the server and moving the directory and restarting it. For user-defined tablespaces, you have to shut down the server and repoint the symlinks in pg_tblspc, and update the pg_tablespace.spclocation, which is removed in PG 9.2. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6712: PostgreSQL 9.2 beta2: alter table drop constraint does not work on inherited master table
On Mon, Jul 2, 2012 at 04:16:31PM +0530, Amit Kapila wrote: From: pgsql-bugs-ow...@postgresql.org [mailto:pgsql-bugs-ow...@postgresql.org] On Behalf Of miroslav.s...@fordfrog.com Sent: Saturday, June 30, 2012 4:28 PM The following bug has been logged on the website: Bug reference: 6712 Logged by: Miroslav Šulc Email address: miroslav.s...@fordfrog.com PostgreSQL version: Unsupported/Unknown Operating system: Gentoo Linux Description: here is the test case: test=# create table test_constraints (id int, val1 varchar, val2 int, unique (val1, val2)); NOTICE: CREATE TABLE / UNIQUE will create implicit index test_constraints_val1_val2_key for table test_constraints CREATE TABLE test=# create table test_constraints_inh () inherits (test_constraints); CREATE TABLE test=# alter table only test_constraints drop constraint test_constraints_val1_val2_key; ERROR: constraint test_constraints_val1_val2_key of relation test_constraints_inh does not exist postgresql tries to drop the constraint even from descendant table though only is specified. In function ATExecDropConstraint(), for the constarint test_constraints_val1_val2_key con-connoinherit is false, due to which it tries to drop the constrint from child table as well. I have checked that from function index_constraint_create() when it calls function CreateConstraintEntry(), the flag for noinherit passed is false. I think this is the reason of failure for the same. I believe this if fixed in all current versions of Postgres. I tested 9.1, 9.2rc1, and 9.3. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] [BUGS] BUG #6572: The example of SPI_execute is bogus
On Sun, Apr 15, 2012 at 12:29:39PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Apr 5, 2012 at 2:39 AM, Hitoshi Harada umi.tan...@gmail.com wrote: On Wed, Apr 4, 2012 at 8:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: Given the lack of complaints since 9.0, maybe we should not fix this but just redefine the new behavior as being correct? But it seems mighty inconsistent that the tuple limit would apply if you have RETURNING but not when you don't. In any case, the ramifications are wider than one example in the SPI docs. To be honest, I was surprised when I found tcount parameter is said to be applied to even INSERT. I believe people think that parameter is to limit memory consumption when returning tuples thus it'd be applied for only SELECT or DML with RETURNING. So I'm +1 for non-fix but redefine the behavior. Who wants to limit the number of rows processed inside the backend, from SPI? Yeah. Okay, apparently nobody cares about RETURNING behaving differently from non-RETURNING, so the consensus is to redefine the current behavior as correct. That means what we need is to go through the docs and see what places need to be updated (and, I guess, back-patch the changes to 9.0). I will get to this if nobody else does, but not right away. Would someone make the doc change outlined above? Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [ADMIN] Repeatable crash in pg_dump (with -d2 info)
On Tue, Jan 17, 2012 at 04:46:50PM -0500, David Schnur wrote: I finally had time to test this further on a variety of systems, and was unable to reproduce on any non-Windows platform. The dump even works fine on Windows XP; just not Windows 7. This prompted me to do a little more research, and this time I found this thread from Sept. 2011: http://postgresql.1045698.n5.nabble.com/ BUG-6233-pg-dump-hangs-with-Access-Violation-C005-td4851598.html From Tom Lane in the above thread: Hmm. I can see how that would happen if you're using one of the Windows environments wherein malloc's done inside libpq have to be free'd inside libpq. (The PQExpBuffer support code is in libpq...) However, the flaw in that explanation is that it would basically mean pg_dump doesn't work at all on Windows, at least not if you have any user-defined functions, and probably some other cases too because there seem to be multiple instances of the dubious coding. It's a bit hard to believe that nobody's noticed that before. This appears to describe exactly the issue I'm encountering, and my build is in fact linked against the static runtime. I guess the reason this hasn't come up sooner is because most Windows users either use the 'official' binaries rather than compiling from source, or link against the dynamic runtime. Is this something I could expect to be fixed in the near future, or is it enough of an edge case that I should come up with some solution or work-around on my own? Thanks, Late reply, but I don't see any way we could fix this easily. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6401: IS DISTINCT FROM improperly compares geomoetric datatypes
Is this a TODO? --- On Thu, Jan 19, 2012 at 10:39:42AM -0500, Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Heikki Linnakangas's message of jue ene 19 07:25:36 -0300 2012: Frankly that's such a rare corner case that I'm not very enthusiastic about fixing it. One idea would be to look up the type's b-tree sort operators, and pick the equality operator from there. But point datatype doesn't have b-tree sort operators, either, so it wouldn't help in this case. It doesn't have a hash opclass either, which could be used as a fallback in case there's no btree. Point cannot obviously have a btree opclass (no inequalities), but a hash one seems possible. I think the use case of IS NOT DISTINCT FROM for rowtypes in triggers is a valid one. Note that IS [NOT] DISTINCT is not the only place that assumes that it should use an operator named =. There's also scalar IN, the simple form of CASE, and possibly some others that I forget at the moment. IMO, if we're going to change the semantics of any of these, we should do them all together. This is something I've kinda wanted to do for a long time, but never gotten around to. We've managed to clean up hard-wired assumptions about operator names in a lot of other places, but these syntactic constructs still do it by name. One argument against changing it is that arguably doing so would violate the letter of the SQL standard. For example, I observe that SQL defines the IN construct thus: The expression RVC IN IPV is equivalent to RVC = ANY IPV (SQL99 8.4 in predicate syntax rule 4). The word equality appears nowhere in the definition of IN. Thus, if we take X IN (Y,Z,Q) and implement it with some operator not named =, we have not done what the spec clearly says to do. Now you can make the case that we'd be implementing the spirit rather than the letter of the spec, but that's a rather shaky case to have to make. The same is true for simple CASE: c) The case specification is equivalent to a searched case in which each searched when clause specifies a search condition of the form CO=WO. with absolutely no hint that equality is what the = symbol is supposed to get you. And in 8.13 distinct predicate we have Case: i) X IS DISTINCT FROM Y is false if either: 1) X and Y are the null value, or 2) X = Y according to Subclause 8.2, comparison predicate. ii) Otherwise, X IS DISTINCT FROM Y is true. which at least suggests that what's wanted is equality, but they're still defining it in terms of an operator named = (and AFAICS subclause 8.2 doesn't address the possibility that X=Y could mean something other than the common idea of equality). So on the whole, it might be better to just provide an operator named = for point, and not open up the can of worms about whether these constructs should use some other rule for deciding which operator to compare with. 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 -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6412: psql fe-connect truncate passwords
+115,34 @@ simple_prompt(const char *prompt, int maxlen, bool echo)
fflush(termout);
}
- if (fgets(destination, maxlen + 1, termin) == NULL)
- destination[0] = '\0';
-
- length = strlen(destination);
- if (length 0 destination[length - 1] != '\n')
- {
- /* eat rest of the line */
- charbuf[128];
- int buflen;
+ if (maxlen 0) {
+ if (fgets(destination, maxlen + 1, termin) == NULL)
+ destination[0] = '\0';
+ length = strlen(destination);
+ if (length 0 destination[length - 1] != '\n')
+ {
+ /* eat rest of the line */
+ do
+ {
+ if (fgets(buf, bufsize, termin) == NULL)
+ break;
+ buflen = strlen(buf);
+ } while (buflen 0 buf[buflen - 1] != '\n');
+ }
+
+ } else {
do
{
- if (fgets(buf, sizeof(buf), termin) == NULL)
+ if (fgets(buf, bufsize, termin) == NULL)
break;
buflen = strlen(buf);
+ destination = realloc( destination,
strlen(destination)+1+buflen );
+ /* Out of memory ? */
+ if( !destination )
+ return NULL;
+ strcat( destination, buf );
+ length = strlen(destination);
} while (buflen 0 buf[buflen - 1] != '\n');
}
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #6412: psql fe-connect truncate passwords
On Sat, Jan 28, 2012 at 01:47:04PM -0500, Tom Lane wrote: Euler Taveira de Oliveira eu...@timbira.com writes: I don't see it as a bug but a limitation. Why do you need such a long password? Yeah, I think the reason we're not too consistent about this is that nobody ever imagined that limits of 100 bytes or more would pose an issue in practice. What's the use-case for passwords longer than that? Thanks for all the feedback. I know it is a pain for me to re-ask these questions, but it allows us to know that these issues have gotten sufficient thought. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6489: Alter table with composite type/table
On Wed, Mar 14, 2012 at 07:19:14PM +0100, Rikard Pavelic wrote: On 13.3.2012. 20:49, Merlin Moncure wrote: I personally think it's an oversight. This was just discussed a couple of days ago here: http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-column-td5544844.html The server is blocking the alter-not-null-with-default because it's assuming that the default should be applied to dependent (foreign) tables implementing the type as a field. I think this assumption is totally bogus because composite types defaults get applied to the type, not to member fields and therefore a default has no meaning in that context. I think the TODO should read to relax the check essentially. merlin I agree. TODO: alter table-type columns according to attribute type rules. Enforce only TYPE features and ignore TABLE features when altering composite table-types. While I'm making up TODO's, my favorite one: support recursive types. Should we add this TODO? I am confused by the text above though. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6372: Error while creating database with fsync parameter as on incase of CIFS
On Mon, Jan 2, 2012 at 04:00:19PM -0500, Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Tom Lane's message of lun ene 02 17:28:33 -0300 2012: it seems like EINVAL is a considerably more reasonable thing to return than EBADF, if the filesystem is trying to tell you that it won't fsync a directory. So I'm a bit surprised this question hasn't come up for other filesystems. Probably because other filesystems do allow you to fsync directories. In fact for some cases they _require_ it ... remember the fiasco when MTA writers were told that they needed to fsync their queue dirs in order for all queued email to persist? Yeah, the long and the short of it is that if the filesystem won't accept an fsync on a directory, we have to assume that it doesn't need it and will manage metadata persistence safely without prodding. The only real question here is whether an EINVAL could mean something besides fsync on directory is not accepted. If there are any scenarios where it represents a transient/fixable error, then we'd want to report it. It's far from clear to me that there are any though. What it could mean in general is not at issue, because we know the target is a directory that we just created moments before. I assume this never got resolved. Should it be changed to ignore EINVAL? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [PATCH] Prevent hanging on unreachable hosts on startup
On Thu, Jan 5, 2012 at 09:23:55AM -0500, Ryan Kelly wrote:
On Wed, Jan 04, 2012 at 09:36:57PM -0500, Tom Lane wrote:
Ryan P. Kelly rpkell...@gmail.com writes:
The signal handler installed by setup_cancel_handler() will ignore
attempts to exit psql should a host be unreachable.
Hm. That may be worth doing something about, but the proposed patch
seems more like a quick-and-dirty hack than a solution. The main
thing I don't like about it is that if we care about this scenario
during initial startup, we should also care about it during a \c
command, but just delaying the installation of the signal handler
won't fix that case.
Sure, but do you still think moving the signal handler down is correct?
I've attached another patch that handles interrupting \c. I also noticed
there appears to be what looks like duplication of code in startup.c and
command.c in main() and do_connect() respectively. I'm wondering if I
they should be made to share the same code which (with my patch) would
then mean the signal handler could be left where it was.
More generally, what if the server becomes unreachable mid-session?
I'm not sure how much there is to be done about that case, since
there's probably no good way to distinguish it from a query that
takes a really long time. But if we're going to think about this,
we might as well think about all the cases.
Well in this case we can still interrupt the query, no? This will drop
us back into the prompt, where we can safely \q.
Tom, can you comment on this patch because you commented on the previous
version? Thanks.
---
From 8f9d0b5088021d944aceac65a96d7bd0c24aa0c6 Mon Sep 17 00:00:00 2001
From: Ryan P. Kelly rpkell...@gmail.com
Date: Thu, 5 Jan 2012 09:13:38 -0500
Subject: [PATCH] Allow interrupting hanging \c connection attempts
---
src/bin/psql/command.c | 17 +
1 files changed, 13 insertions(+), 4 deletions(-)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 69fac83..845705d 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1516,7 +1516,7 @@ static bool
do_connect(char *dbname, char *user, char *host, char *port)
{
PGconn *o_conn = pset.db,
-*n_conn;
+*n_conn = NULL;
char *password = NULL;
if (!dbname)
@@ -1570,7 +1570,13 @@ do_connect(char *dbname, char *user, char *host, char
*port)
keywords[7] = NULL;
values[7] = NULL;
- n_conn = PQconnectdbParams(keywords, values, true);
+ if (sigsetjmp(sigint_interrupt_jmp, 1) != 0) {
+ /* got here with longjmp */
+ } else {
+ sigint_interrupt_enabled = true;
+ n_conn = PQconnectdbParams(keywords, values, true);
+ sigint_interrupt_enabled = false;
+ }
free(keywords);
free(values);
@@ -1600,7 +1606,8 @@ do_connect(char *dbname, char *user, char *host, char
*port)
*/
if (pset.cur_cmd_interactive)
{
- psql_error(%s, PQerrorMessage(n_conn));
+ if (n_conn)
+ psql_error(%s, PQerrorMessage(n_conn));
/* pset.db is left unmodified */
if (o_conn)
@@ -1608,7 +1615,9 @@ do_connect(char *dbname, char *user, char *host, char
*port)
}
else
{
- psql_error(\\connect: %s, PQerrorMessage(n_conn));
+ if (n_conn)
+ psql_error(\\connect: %s,
PQerrorMessage(n_conn));
+
if (o_conn)
{
PQfinish(o_conn);
--
1.7.7.4
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] 'pg_ctl restart' confused about pathname to postgresql.conf
I have applied the attached doc patch to document the problem with relative paths and pg_ctl restart. --- On Sun, Oct 23, 2011 at 08:49:25PM -0400, Josh Kupershmidt wrote: On Sat, Oct 22, 2011 at 12:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think the reason it has a problem is that this is what's left in postmaster.opts: /home/tgl/pgsql/bin/postgres -D baz (which is an accurate representation of the command line from startup) and that -D switch gets fed to the postmaster as-is during restart. I see. By and large, I would not recommend using a relative pathname to start the postmaster, unless you plan to start it from the same working directory every time. Well, now I know. But that really seems like an annoying and arbitrary restriction, not to mention not being documented anywhere AFAICT. (I came upon this problem because I often set up servers with binaries, libraries, and $PGDATA all tucked away under /home/postgres/, and it seemed natural to use a relative pathname as my data directory argument to pg_ctl since my working directory will usually be /home/postgres/ when I'm poking at the server.) We could possibly avoid this by having pg_ctl try to absolute-ify the -D setting during postmaster start, but I'm not convinced it's worth the trouble, or even that it's appropriate for pg_ctl to editorialize on the user's choice of absolute vs relative path. I don't want to bikeshed on the mechanics of how exactly this should work, but it doesn't seem like it should be so hard to get this to DWIM. In the example I posted, the last step which fails is basically: pg_ctl -D /tmp/foo/bar/baz/ restart and it just seems totally broken for that to not work: pg_ctl knows exactly which data directory the user means when invoked here. Plus, these steps would work fine instead at that point: pg_ctl -D /tmp/foo/bar/baz/ stop pg_ctl -D /tmp/foo/bar/baz/ start and I was under the impression (supported by the pg_ctl doc page, which claims restart mode effectively executes a stop followed by a start) that these sequences should be equivalent. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/pg_ctl-ref.sgml b/doc/src/sgml/ref/pg_ctl-ref.sgml new file mode 100644 index 7a4c2be..3107514 *** a/doc/src/sgml/ref/pg_ctl-ref.sgml --- b/doc/src/sgml/ref/pg_ctl-ref.sgml *** PostgreSQL documentation *** 188,194 para optionrestart/option mode effectively executes a stop followed by a start. This allows changing the commandpostgres/command !command-line options. /para para --- 188,196 para optionrestart/option mode effectively executes a stop followed by a start. This allows changing the commandpostgres/command !command-line options. optionrestart/option might fail if !relative paths specified were specified on the command-line during !server start. /para para -- 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] Cannot dump 8.4.8 database using later versions
I don't think we ever addressed this, but since we have had minimal complaints about it, I guess we are OK. --- On Tue, Nov 15, 2011 at 10:04:57PM -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Nov 15, 2011 at 5:16 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: NEW used to be a reserved keyword, but it's not so in 9.0 anymore. So 9.0 pg_dump thinks it doesn't need to be quoted. Why isn't it correct? It's correct to not quote it in pg_dump's output (since we make no promises that such output would load into a pre-9.0 server anyway). The problem is that it needs to be quoted in commands that pg_dump sends back to the 8.4 server. Example: psql (8.4.9) You are now connected to database db84. db84=# create table new( f1 int, new text); ... pg_dump with newer pg_dump ... pg_dump: SQL command failed pg_dump: Error message from server: ERROR: syntax error at or near new LINE 1: COPY public.new (f1, new) TO stdout; ^ pg_dump: The command was: COPY public.new (f1, new) TO stdout; The least painful solution might be to always quote *every* identifier in commands sent to the source server, since we don't especially care how nice-looking those are. regards, tom lane -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6067: In PL/pgsql, EXISTS(SELECT ... INTO...) fails
On Wed, Nov 30, 2011 at 03:36:11PM -0500, Robert Haas wrote: On Tue, Nov 29, 2011 at 9:32 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: David Fetter dfet...@vmware.com writes: IF EXISTS (SELECT 1 INTO STRICT i) THEN RAISE NOTICE '%', a; END IF; Umm ... are you just complaining that the error message isn't very helpful, or are you actually expecting that to do something useful? If the latter, what exactly? I'm particularly confused by your use of the STRICT option here, because if we did support that, I would expect the STRICT to throw an error if there were not exactly one matching row, making the EXISTS test 100% pointless. But the short answer is that we don't support INTO in sub-selects, and in general I doubt that we ever will, since in most cases the behavior wouldn't be very well-defined. It might be worth a TODO to provide a better error message than syntax error, though. Is it worth documenting, fixing, or adding this to the TODO list? At most I would say we could try to improve the error message. I researched this and it seems to complex to improve the error message. I am afraid it would have to bleed into the main backend parser. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #7494: WAL replay speed depends heavily on the shared_buffers size
On Wed, Aug 15, 2012 at 10:10:42AM +, val...@gmail.com wrote: The following bug has been logged on the website: Bug reference: 7494 Logged by: Valentine Gogichashvili Email address: val...@gmail.com PostgreSQL version: 9.0.7 Operating system: Linux version 2.6.32-5-amd64 (Debian 2.6.32-41) Description: We are experiencing strange(?) behavior on the replication slave machines. The master machine has a very heavy update load, where many processes are updating lots of data. It generates up to 30GB of WAL files per hour. Normally it is not a problem for the slave machines to replay this amount of WAL files on time and keep on with the master. But at some moments, the slaves are “hanging” with 100% CPU usage on the WAL replay process and 3% IOWait, needing up to 30 seconds to process one WAL file. If this tipping point is reached, then a huge WAL replication lag is building up quite fast, that also leads to overfill of the XLOG directory on the slave machines, as the WAL receiver is putting the WAL files it gets via streaming replication the XLOG directory (that, in many cases are quite a limited size separate disk partition). What we noticed also, is that reducing shared_buffers parameter from our normal 20-32 GB for the slave machines, to 2 GB increases the speed of WAL replay dramatically. After restart of the slave machine with much lower shared_buffers values, the replay becomes up to 10-20 times faster. On the attached graph, there is a typical graph of WAL replication delay for one of the slaves. In that graph small (up to 6GB) replication delay peaks during the night are caused by some long running transactions, stopping WAL replay on this slave, to prevent replication collisions. But the last, big peaks are sometimes start because of that waiting for a long running transaction on the slave, but then they are growing as described above. I know, that there is only one process that replays data, generated by many threads on master machine. But why does the replay performance depend so much on the shared_buffers parameter and can it be optimized? We warn against making shared buffers 8GB, and this is perhaps another good reason. The problem is probably due to the shared buffers filling up with lots of dirty data, and the kernel being unable to contain all the data coming during a checkpoint. It is also possible that the buffer management overhead is just too high for that many buffers. It is also possible that Postgres 9.1 or the coming 9.2 would do better in this regard. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6162: initdb : Windows username isn't escaped
On Sun, Aug 14, 2011 at 11:25:22AM +, Emmanuel Guyot wrote:
The following bug has been logged online:
Bug reference: 6162
Logged by: Emmanuel Guyot
Email address: emmanuel.gu...@gmail.com
PostgreSQL version: 8.2.3
Operating system: Windows 7
Description:initdb : Windows username isn't escaped
Details:
Initdb crashes when I use it with a windows username that has a quote inside
(e.g. : L'élixir)
Here is the error reported :
initialisation des droits sur les objets internes... 2010-05-14 17:01:25.434
FATAL: syntax error at or near élixir at character 44
2010-05-14 17:01:25.434 STATEMENT: UPDATE pg_class SET relacl =
E'{=r/\\L'élixir\\}' WHERE relkind IN ('r', 'v', 'S') AND relacl IS
NULL;
If I force the username as an option with the escaped value L\'élixir the
initdb works fine, but I can't start the server. So I can't find a
workaround.
I've watched the newer sources of initdb and I can't see any change for this
bug.
I have fixed the problem with the attached, applied patch, which will
appear in Postgres 9.3. The problem was that single-quotes in usernames
were not properly escaped by initdb.
Also, I have improved the pg_hba.conf documentation, and added an assert
to catch future breakage.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/client-auth.sgml b/doc/src/sgml/client-auth.sgml
new file mode 100644
index 679c40a..9771394
*** a/doc/src/sgml/client-auth.sgml
--- b/doc/src/sgml/client-auth.sgml
***
*** 80,86
Records cannot be continued across lines.
A record is made
up of a number of fields which are separated by spaces and/or tabs.
!Fields can contain white space if the field value is quoted.
Quoting one of the keywords in a database, user, or address field (e.g.,
literalall/ or literalreplication/) makes the word lose its special
character, and just match a database, user, or host with that name.
--- 80,86
Records cannot be continued across lines.
A record is made
up of a number of fields which are separated by spaces and/or tabs.
!Fields can contain white space if the field value is double-quoted.
Quoting one of the keywords in a database, user, or address field (e.g.,
literalall/ or literalreplication/) makes the word lose its special
character, and just match a database, user, or host with that name.
diff --git a/src/backend/parser/scansup.c b/src/backend/parser/scansup.c
new file mode 100644
index 6101457..b8e2f71
*** a/src/backend/parser/scansup.c
--- b/src/backend/parser/scansup.c
*** scanstr(const char *s)
*** 56,61
--- 56,63
* appear in pairs, so there should be another character.
*/
i++;
+ /* The bootstrap parser is not as smart, so check here. */
+ Assert(s[i] == '\'');
newStr[j] = s[i];
}
else if (s[i] == '\\')
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
new file mode 100644
index 132ad0f..a53760a
*** a/src/bin/initdb/initdb.c
--- b/src/bin/initdb/initdb.c
*** bootstrap_template1(void)
*** 1395,1401
bki_lines = replace_token(bki_lines, FLOAT8PASSBYVAL,
FLOAT8PASSBYVAL ? true : false);
! bki_lines = replace_token(bki_lines, POSTGRES, username);
bki_lines = replace_token(bki_lines, ENCODING, encodingid);
--- 1395,1401
bki_lines = replace_token(bki_lines, FLOAT8PASSBYVAL,
FLOAT8PASSBYVAL ? true : false);
! bki_lines = replace_token(bki_lines, POSTGRES, escape_quotes(username));
bki_lines = replace_token(bki_lines, ENCODING, encodingid);
*** setup_privileges(void)
*** 2043,2050
PG_CMD_OPEN;
! priv_lines = replace_token(privileges_setup,
! $POSTGRES_SUPERUSERNAME, username);
for (line = priv_lines; *line != NULL; line++)
PG_CMD_PUTS(*line);
--- 2043,2050
PG_CMD_OPEN;
! priv_lines = replace_token(privileges_setup, $POSTGRES_SUPERUSERNAME,
! escape_quotes(username));
for (line = priv_lines; *line != NULL; line++)
PG_CMD_PUTS(*line);
*** main(int argc, char *argv[])
*** 3056,3062
canonicalize_path(pg_data);
#ifdef WIN32
-
/*
* Before we execute another program, make sure that we are running with a
* restricted token. If not, re-execute ourselves with one.
--- 3056,3061
--
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 #6165: documentation bug in plpgsql-declarations.html and plpgsql-statements.html (or plpgsql parser bug)
On Mon, Aug 22, 2011 at 04:17:04PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Aug 17, 2011 at 1:21 AM, raf r...@raf.org wrote: so, there is either a plpgsql parser bug that treats the sql equality operator as the plpgsql assignment operator, or = is an undocumented alternative to the documented plpgsql assignment operator (:=). I think it's the latter. It's definitely intentional, not a bug, so far as the source code is concerned: assign_operator : '=' | COLON_EQUALS ; I have a vague recollection that we might have left that undocumented on purpose, but I'm not actually sure why we support it in the first place. I think it's legacy at this point. We have discussed before whether to document it, and IIRC the general feeling was if we do document it, we'll never be able to get rid of it. Whether we could get rid of it now (instead of documenting it) was not seriously discussed. I've seen at least a few people saying that they do rely on it ... I think the question is whether '=' is used enough that we have to mention that it is a non-standard extension that might be removed someday, or something. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6178: date_trunc : interval units week not supported contradicts documentation
On Thu, Aug 25, 2011 at 07:05:34PM +, Noah Hamerslough wrote:
The following bug has been logged online:
Bug reference: 6178
Logged by: Noah Hamerslough
Email address: n...@pcc.com
PostgreSQL version: 8.4
Operating system: Windows Vista
Description:date_trunc : interval units week not supported
contradicts documentation
Details:
http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-
DATETIME-TRUNC
The documentation for date_trunc('field', source) lists 'week' in the as a
valid value for 'field' However, if the source is an interval, 'week' is not
supported.
select date_trunc('week', '1 month 15 days'::interval);
ERROR: interval units week not supported
SQL state: 0A000
Either 'week' should be supported or the documentation should be updated to
reflect that it is not.
Turns out the reason we don't support this is because there are usually
a fractional number of weeks in a month, so there is no good way to do
this for intervals. I have applied the attached patch to PG 9.3 which
will explain why this is not supported. I saw this as better than a
documentation mention.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
new file mode 100644
index 8593b6b..2adc178
*** a/src/backend/utils/adt/timestamp.c
--- b/src/backend/utils/adt/timestamp.c
*** interval_trunc(PG_FUNCTION_ARGS)
*** 3710,3719
break;
default:
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg(interval units \%s\ not supported,
! lowunits)));
}
if (tm2interval(tm, fsec, result) != 0)
--- 3710,3726
break;
default:
! if (val == DTK_WEEK)
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg(interval units \%s\ not supported
! because months usually have fractional weeks,
! lowunits)));
! else
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg(interval units \%s\ not supported,
! lowunits)));
}
if (tm2interval(tm, fsec, result) != 0)
--
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] psql doesn't reuse -p after backend fail
On Wed, Sep 14, 2011 at 10:52:50PM -0500, Robert Haas wrote:
On Thu, Sep 8, 2011 at 5:09 AM, Peter Eisentraut pete...@gmx.net wrote:
On tis, 2011-09-06 at 17:12 +0200, hubert depesz lubaczewski wrote:
On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
It's not just the port, it's all the connection parameters ---
do_connect relies on the PGconn object to remember those, and in this
case there no longer is a PGconn object.
We could have psql keep that information separately, but I'm not sure
it's really worth the trouble.
well, I think it's definitely worth the trouble. If I had datbaase
standing at 5432, it would connect to it, and then I could mistakenly
ran commands to wrong database.
this is clearly not a good thing.
Perhaps just prevent \connect without argument if the information is no
longer available.
I think it'd be worth actually having psql maintain the information
separately from the PGconn... but if nobody feels motivated to go do
that, doing at least this much would remove the foot-gun. So +1 for
that.
OK, I have applied the attached, applied patch to do as you suggest.
Here are examples:
! SELECT * FROM mytable WHERE to_ascii(convert_to(mytext, 'latin1'),
'latin1')
- = to_ascii(convert_to('nicetry', 'latin1'), 'latin1');
You are currently not connected to a database.
! \c
All connection parameters must be supplied because no database
connection exists
! \q
$ psql -p 5433 test
psql (9.3devel)
Type help for help.
test= \c
You are now connected to database test as user postgres.
test= \q
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
new file mode 100644
index 6ead800..2cd3ab4
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** do_connect(char *dbname, char *user, cha
*** 1512,1517
--- 1512,1523
*n_conn;
char *password = NULL;
+ if (!o_conn (!dbname || !user || !host || !port))
+ {
+ fputs(_(All connection parameters must be supplied because no database connection exists\n), stderr);
+ return false;
+ }
+
if (!dbname)
dbname = PQdb(o_conn);
if (!user)
--
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 #6150: commit_delay should have unit ms
On Thu, Aug 4, 2011 at 09:30:35PM +, Christoph Anton Mitterer wrote:
The following bug has been logged online:
Bug reference: 6150
Logged by: Christoph Anton Mitterer
Email address: cales...@scientia.net
PostgreSQL version: 0.9
Operating system: Debian sid
Description:commit_delay should have unit ms
Details:
Hi.
AFAIK form the documentation, the setting commit_delay, should have the base
unit ms.
However, when doing something like
$ psql -c 'SELECT name,vartype,unit FROM pg_settings;' | grep commit_delay
I get:
could not change directory to /root/test
commit_delay| integer |
No unit.
Yes, you are correct. The attached, applied patch for Postgres 9.3 will
properly display units. I checked all the other variables and they all
had proper units.
I also removed an unnecessary units designation in
postgresql.conf.sample for a zero value --- if we want to put units on
zero values, we should do it consistently in a separate patch.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
new file mode 100644
index 80e5aa1..a1d303b
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
*** static struct config_int ConfigureNamesI
*** 2033,2039
{commit_delay, PGC_USERSET, WAL_SETTINGS,
gettext_noop(Sets the delay in microseconds between transaction commit and
flushing WAL to disk.),
! NULL
},
CommitDelay,
0, 0, 10,
--- 2033,2040
{commit_delay, PGC_USERSET, WAL_SETTINGS,
gettext_noop(Sets the delay in microseconds between transaction commit and
flushing WAL to disk.),
! NULL,
! GUC_UNIT_MS
},
CommitDelay,
0, 0, 10,
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
new file mode 100644
index c24afb0..adcbcf6
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***
*** 137,143
# - Cost-Based Vacuum Delay -
! #vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-1 credits
#vacuum_cost_page_miss = 10 # 0-1 credits
#vacuum_cost_page_dirty = 20 # 0-1 credits
--- 137,143
# - Cost-Based Vacuum Delay -
! #vacuum_cost_delay = 0 # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-1 credits
#vacuum_cost_page_miss = 10 # 0-1 credits
#vacuum_cost_page_dirty = 20 # 0-1 credits
--
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 #6150: commit_delay should have unit ms
On Tue, Aug 14, 2012 at 04:18:40PM -0400, Bruce Momjian wrote: On Thu, Aug 4, 2011 at 09:30:35PM +, Christoph Anton Mitterer wrote: The following bug has been logged online: Bug reference: 6150 Logged by: Christoph Anton Mitterer Email address: cales...@scientia.net PostgreSQL version: 0.9 Operating system: Debian sid Description:commit_delay should have unit ms Details: Hi. AFAIK form the documentation, the setting commit_delay, should have the base unit ms. However, when doing something like $ psql -c 'SELECT name,vartype,unit FROM pg_settings;' | grep commit_delay I get: could not change directory to /root/test commit_delay| integer | No unit. Yes, you are correct. The attached, applied patch for Postgres 9.3 will properly display units. I checked all the other variables and they all had proper units. I also removed an unnecessary units designation in postgresql.conf.sample for a zero value --- if we want to put units on zero values, we should do it consistently in a separate patch. Opps, turns out the units are microseconds (as pointed out to me by Magnus), and we don't have a microsecond designation in that file, so I reverted that and just added a C comment about why it has no units. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] ERROR - Upgrading Postgresql 8.4.3 to Postgressql 9.1.4
On Tue, Aug 14, 2012 at 04:27:45PM -0400, Freddie Burgess wrote:
We are trying to upgrade our production postgres database instance that is
approximately 4TB in size and it contains various table partition structures.;
a separate tablespace for each partition. We were initially successful in
performing the upgrade using pg_upgrade with both the link and copy options in
our development environment, but that environment has a different hardware
configuration {DAS storage and centOS operating system and dedicated server}
than what is configured in production.
When we attempted to upgrade using pg_upgrade with the link option on our
production database. Our Production database is configured with RHEL 5.4, NAS
storage; configured utilizing a symlink that references the file system on the
network, and VMWare.
We ran the pg_upgrade procedures, and it completed to quickly (less than 10
mins)
That is normal for link mode --- it is using hard links for the data
files.
- pg_upgrade (using the link option) is supposed to merge the legacy (8.4.3)
DB
cluster with the new, empty (9.1.4) cluster.
Yes.
- Both databases clusters were being identified as 8.4.3 clusters AFTER the
upgrade. This was strange, we do not have an explanation. It acted as if the
upgrade downgraded the 9.1.4 cluster to 8.4.3.
That is very odd, and I have never heard of that problem before.
- At completion, the DB files were unusable. They would not start under 9.1.4
or 8.4.3.
Could our production server’s hardware configuration possibility be the
culprit
responsible for this upgrade failure?
I can't see how, unless you were somehow using the NAS in a way that
didn't allow hard links, but I am unclear how that would happen, and
pg_upgrade checks for that.
I guess the big question is how is production different from testing,
and how does the new server identify itself as 8.4 if you can't connect
to it. Something is obviously majorly messed up.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #6150: commit_delay should have unit ms
On Tue, Aug 14, 2012 at 05:26:39PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Opps, turns out the units are microseconds (as pointed out to me by Magnus), and we don't have a microsecond designation in that file, so I reverted that and just added a C comment about why it has no units. Is it worth adding support for microseconds in there? Not sure. Technically it wouldn't be very hard, but I seem to recall this was discussed before and we were worried about whether people would be confused about what ms means. Don't know that us would be universally recognized, either. Yes, I do remember the discussion. Because commit_delay is so rarely used, and the only one that uses usec units by default, it didn't seem to make sense to add documentation for an additional unit. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6126: CC parameter in to_char() behaves incorrectly
On Mon, Aug 6, 2012 at 03:20:18PM -0400, Bruce Momjian wrote:
On Sat, Aug 4, 2012 at 10:34:14AM -0400, Bruce Momjian wrote:
I am thinking it is too late to apply this for 9.2 because users might
have already tested their applications, though I doubt many are using BC
dates. Feedback?
There is never just one bug in formatting.c --- the input side was also
broken for BC/negative centuries. The attached patch fixes the input
side as well, and shows the old/fixed output for BC centuries. The test
queries were:
SELECT to_date('-6 4', 'CC YY');
SELECT to_date(' 6 4', 'CC YY');
SELECT to_date('-6 00', 'CC YY');
SELECT to_date(' 6 00', 'CC YY');
SELECT to_date('-6', 'CC');
SELECT to_date(' 6', 'CC');
I believe this is all for 9.3-only.
OK, I found another bug in our code; the output of:
SELECT to_date(' 6 BC', 'CC BC');
returned 501BC, instead of the start of the century, 600BC, like SELECT
to_date('-6', 'CC') does.
I also allowed negative BC dates to map to AD dates, just like negative
AD dates map to BC dates.
Attached is an updated patch and output diff.
Applied.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #6126: CC parameter in to_char() behaves incorrectly
On Sat, Aug 4, 2012 at 10:34:14AM -0400, Bruce Momjian wrote:
I am thinking it is too late to apply this for 9.2 because users might
have already tested their applications, though I doubt many are using BC
dates. Feedback?
There is never just one bug in formatting.c --- the input side was also
broken for BC/negative centuries. The attached patch fixes the input
side as well, and shows the old/fixed output for BC centuries. The test
queries were:
SELECT to_date('-6 4', 'CC YY');
SELECT to_date(' 6 4', 'CC YY');
SELECT to_date('-6 00', 'CC YY');
SELECT to_date(' 6 00', 'CC YY');
SELECT to_date('-6', 'CC');
SELECT to_date(' 6', 'CC');
I believe this is all for 9.3-only.
OK, I found another bug in our code; the output of:
SELECT to_date(' 6 BC', 'CC BC');
returned 501BC, instead of the start of the century, 600BC, like SELECT
to_date('-6', 'CC') does.
I also allowed negative BC dates to map to AD dates, just like negative
AD dates map to BC dates.
Attached is an updated patch and output diff.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 4347ad3..6bd3fc6
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*** DCH_to_char(FormatNode *node, bool is_in
*** 2640,2647
case DCH_CC:
if (is_interval) /* straight calculation */
i = tm-tm_year / 100;
! else /* century 21 starts in 2001 */
! i = (tm-tm_year - 1) / 100 + 1;
if (i = 99 i = -99)
sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, i);
else
--- 2640,2654
case DCH_CC:
if (is_interval) /* straight calculation */
i = tm-tm_year / 100;
! else
! {
! if (tm-tm_year 0)
! /* Century 20 == 1901 - 2000 */
! i = (tm-tm_year - 1) / 100 + 1;
! else
! /* Century 6BC == 600BC - 501BC */
! i = tm-tm_year / 100 - 1;
! }
if (i = 99 i = -99)
sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, i);
else
*** do_to_timestamp(text *date_txt, text *fm
*** 3471,3497
*/
if (tmfc.cc tmfc.yysz = 2)
{
tm-tm_year = tmfc.year % 100;
if (tm-tm_year)
! tm-tm_year += (tmfc.cc - 1) * 100;
else
! tm-tm_year = tmfc.cc * 100;
}
else
tm-tm_year = tmfc.year;
}
else if (tmfc.cc) /* use first year of century */
- tm-tm_year = (tmfc.cc - 1) * 100 + 1;
-
- if (tmfc.bc)
{
! if (tm-tm_year 0)
! tm-tm_year = -(tm-tm_year - 1);
else
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
! errmsg(inconsistent use of year %04d and \BC\,
! tm-tm_year)));
}
if (tmfc.j)
--- 3478,3511
*/
if (tmfc.cc tmfc.yysz = 2)
{
+ if (tmfc.bc)
+ tmfc.cc = -tmfc.cc;
tm-tm_year = tmfc.year % 100;
if (tm-tm_year)
! {
! if (tmfc.cc = 0)
! tm-tm_year += (tmfc.cc - 1) * 100;
! else
! tm-tm_year = (tmfc.cc + 1) * 100 - tm-tm_year + 1;
! }
else
! tm-tm_year = tmfc.cc * 100 + ((tmfc.cc = 0) ? 0 : 1);
}
else
+ {
tm-tm_year = tmfc.year;
+ if (tmfc.bc tm-tm_year 0)
+ tm-tm_year = -(tm-tm_year - 1);
+ }
}
else if (tmfc.cc) /* use first year of century */
{
! if (tmfc.bc)
! tmfc.cc = -tmfc.cc;
! if (tmfc.cc = 0)
! tm-tm_year = (tmfc.cc - 1) * 100 + 1;
else
! tm-tm_year = tmfc.cc * 100 + 1;
}
if (tmfc.j)
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
new file mode 100644
index ab8faab..db2cfe6
*** a/src/test/regress/expected/timestamp.out
--- b/src/test/regress/expected/timestamp.out
*** SELECT '' AS to_char_3, to_char(d1, 'Y,Y
*** 992,998
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
!| 0,097 0097 097 97 7 01 1 02 07 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
--- 992,998
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
!| 0,097 0097 097 97 7 -1 1 02 07 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
*** SELECT '' AS to_char_4, to_char(d1, 'FMY
*** 1063,1069
| 1,997 1997 997 97 7 20 1 2 7 45 14 6
Re: [BUGS] BUG #6126: CC parameter in to_char() behaves incorrectly
On Fri, Aug 3, 2012 at 06:51:45PM -0400, Bruce Momjian wrote:
I also tested boundry values, e.g. 6th Century BC is 600-501:
test= select to_char('0600-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-6
(1 row)
test= select to_char('0599-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-6
(1 row)
test= select to_char('0501-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-6
(1 row)
test= select to_char('0500-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-5
(1 row)
I am thinking it is too late to apply this for 9.2 because users might
have already tested their applications, though I doubt many are using BC
dates. Feedback?
There is never just one bug in formatting.c --- the input side was also
broken for BC/negative centuries. The attached patch fixes the input
side as well, and shows the old/fixed output for BC centuries. The test
queries were:
SELECT to_date('-6 4', 'CC YY');
SELECT to_date(' 6 4', 'CC YY');
SELECT to_date('-6 00', 'CC YY');
SELECT to_date(' 6 00', 'CC YY');
SELECT to_date('-6', 'CC');
SELECT to_date(' 6', 'CC');
I believe this is all for 9.3-only.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 4347ad3..7eb573d
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*** DCH_to_char(FormatNode *node, bool is_in
*** 2640,2647
case DCH_CC:
if (is_interval) /* straight calculation */
i = tm-tm_year / 100;
! else /* century 21 starts in 2001 */
! i = (tm-tm_year - 1) / 100 + 1;
if (i = 99 i = -99)
sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, i);
else
--- 2640,2654
case DCH_CC:
if (is_interval) /* straight calculation */
i = tm-tm_year / 100;
! else
! {
! if (tm-tm_year 0)
! /* Century 20 == 1901 - 2000 */
! i = (tm-tm_year - 1) / 100 + 1;
! else
! /* Century 6BC == 600BC - 501BC */
! i = tm-tm_year / 100 - 1;
! }
if (i = 99 i = -99)
sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, i);
else
*** do_to_timestamp(text *date_txt, text *fm
*** 3473,3487
{
tm-tm_year = tmfc.year % 100;
if (tm-tm_year)
! tm-tm_year += (tmfc.cc - 1) * 100;
else
! tm-tm_year = tmfc.cc * 100;
}
else
tm-tm_year = tmfc.year;
}
else if (tmfc.cc) /* use first year of century */
! tm-tm_year = (tmfc.cc - 1) * 100 + 1;
if (tmfc.bc)
{
--- 3480,3504
{
tm-tm_year = tmfc.year % 100;
if (tm-tm_year)
! {
! if (tmfc.cc = 0)
! tm-tm_year += (tmfc.cc - 1) * 100;
! else
! tm-tm_year = (tmfc.cc + 1) * 100 - tm-tm_year + 1;
! }
else
! tm-tm_year = tmfc.cc * 100 + ((tmfc.cc = 0) ? 0 : 1);
}
else
tm-tm_year = tmfc.year;
}
else if (tmfc.cc) /* use first year of century */
! {
! if (tmfc.cc = 0)
! tm-tm_year = (tmfc.cc - 1) * 100 + 1;
! else
! tm-tm_year = tmfc.cc * 100 + 1;
! }
if (tmfc.bc)
{
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
new file mode 100644
index ab8faab..db2cfe6
*** a/src/test/regress/expected/timestamp.out
--- b/src/test/regress/expected/timestamp.out
*** SELECT '' AS to_char_3, to_char(d1, 'Y,Y
*** 992,998
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
!| 0,097 0097 097 97 7 01 1 02 07 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
--- 992,998
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
!| 0,097 0097 097 97 7 -1 1 02 07 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
*** SELECT '' AS to_char_4, to_char(d1, 'FMY
*** 1063,1069
| 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
| 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
| 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
!| 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042
Re: [BUGS] BUG #6117: psql -c does not work as expected. a documentation bug? a program bug?
On Thu, Jul 14, 2011 at 10:10:28AM +0800, Craig Ringer wrote: On 14/07/2011 4:53 AM, Aleksey Tsalolikhin wrote: The following bug has been logged online: Bug reference: 6117 Logged by: Aleksey Tsalolikhin Email address: atsaloli.t...@gmail.com PostgreSQL version: 8.4.8 Operating system: CentOS 5.5 (64-bit) Description:psql -c does not work as expected. a documentation bug? a program bug? Details: The psql man page says, in the section for the -c option: If the command string contains multiple SQL commands, they are processed in a single transaction However when I run a command string with multiple SQL commands, apparently only the last one is processed. What's happening is that all of them are processed, but only the output for the last one is emitted. I agree that the documentation isn't clear about that, and I'm not sure whether or not that was the intended result. psql -c CREATE TABLE test(x integer); INSERT INTO test(x) VALUES (1),(2),(3); SELECT version(); SELECT * FROM test; x --- 1 2 3 (3 rows) Comments anybody? Just fix the docs, or is this a behaviour issue? I have added documentation for this behavior. Patch attached, and backpatched to 9.2 and 9.1. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml new file mode 100644 index b6bf6a3..1ba5ea8 *** a/doc/src/sgml/ref/psql-ref.sgml --- b/doc/src/sgml/ref/psql-ref.sgml *** PostgreSQL documentation *** 99,105 commandBEGIN//commandCOMMIT/ commands included in the string to divide it into multiple transactions. This is different from the behavior when the same string is fed to !applicationpsql/application's standard input. /para /listitem /varlistentry --- 99,106 commandBEGIN//commandCOMMIT/ commands included in the string to divide it into multiple transactions. This is different from the behavior when the same string is fed to !applicationpsql/application's standard input. Also, only !the result of the last SQL command is returned. /para /listitem /varlistentry -- 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 #6126: CC parameter in to_char() behaves incorrectly
On Thu, Jul 21, 2011 at 08:40:11AM +, Artem Andreev wrote:
The following bug has been logged online:
Bug reference: 6126
Logged by: Artem Andreev
Email address: ar...@oktetlabs.ru
PostgreSQL version: 9.0.4
Operating system: Debian 6.0
Description:CC parameter in to_char() behaves incorrectly
Details:
CC parameter in to_char() behaves incorrectly with BC dates:
1st century BC:
select to_char('0002-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
01
2nd century BC:
select to_char('0101-01-01 00:00:00 BC' :: timestamp, 'CC');
=
to_char
-
00
3rd century BC:
iliran= select to_char('0301-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-2
In all these cases EXTRACT(CENTURY FROM ...) yields the expected result
I have developed the attached patch which fixes this bug:
test= select to_char('0002-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-1
(1 row)
test= select to_char('0101-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-2
(1 row)
test= select to_char('0301-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-4
(1 row)
I also tested boundry values, e.g. 6th Century BC is 600-501:
test= select to_char('0600-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-6
(1 row)
test= select to_char('0599-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-6
(1 row)
test= select to_char('0501-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-6
(1 row)
test= select to_char('0500-01-01 00:00:00 BC' :: timestamp, 'CC');
to_char
-
-5
(1 row)
I am thinking it is too late to apply this for 9.2 because users might
have already tested their applications, though I doubt many are using BC
dates. Feedback?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 4347ad3..6550296
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*** DCH_to_char(FormatNode *node, bool is_in
*** 2640,2647
case DCH_CC:
if (is_interval) /* straight calculation */
i = tm-tm_year / 100;
! else /* century 21 starts in 2001 */
! i = (tm-tm_year - 1) / 100 + 1;
if (i = 99 i = -99)
sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, i);
else
--- 2640,2654
case DCH_CC:
if (is_interval) /* straight calculation */
i = tm-tm_year / 100;
! else
! {
! if (tm-tm_year 0)
! /* Century 20 == 1901 - 2000 */
! i = (tm-tm_year - 1) / 100 + 1;
! else
! /* Century 6BC == 600BC - 501BC */
! i = tm-tm_year / 100 - 1;
! }
if (i = 99 i = -99)
sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, i);
else
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
new file mode 100644
index ab8faab..db2cfe6
*** a/src/test/regress/expected/timestamp.out
--- b/src/test/regress/expected/timestamp.out
*** SELECT '' AS to_char_3, to_char(d1, 'Y,Y
*** 992,998
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
!| 0,097 0097 097 97 7 01 1 02 07 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
--- 992,998
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
| 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
!| 0,097 0097 097 97 7 -1 1 02 07 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
| 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
*** SELECT '' AS to_char_4, to_char(d1, 'FMY
*** 1063,1069
| 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
| 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
| 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
!| 0,097 97 97 97 7 1 1 2 7 47 16 3 1686042
| 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
| 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
| 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778
Re: [BUGS] BUG #6785: Memory Leak in plpgsql
On Mon, Jul 30, 2012 at 05:56:22PM +0200, Andres Freund wrote: Hi, On Monday, July 30, 2012 05:38:07 PM Anderson Valadares wrote: I understand, but the memory should not be returned after the execution of the function? Well, that depends on how memory was allocated by the libc. When it used brk() to allocate memory its rather likely that the memory cannot directly be returned because some block of memory in the new memory is still used by some permanent memory context. Below is the result of running with more than 55,000 calls. PID USER PR NI VIRT SWAP RES CODE DATA SHR S P %CPU %MEMTIME+ COMMAND 618 postgres 15 0 1687m 1.2g 507m 4684 505m 3796 S 1 0.0 4.2 0:04.90 postgres: postgres test [local] idle Interesting. I just let the thing run - by accident - for 30+ minutes and it still hovered at 96MB. FYI, I did a blog entry that mentions when memory is returned: http://momjian.us/main/blogs/pgblog/2012.html#February_1_2012 Specifically, only single memory allocations greater than MMAP_THRESHOLD are returned to the operating system. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6733: All Tables Empty After pg_upgrade (PG 9.2.0 beta 2)
On Sun, Jul 15, 2012 at 02:15:35PM -0700, Mike Wilson wrote: I've had some time to examine this closer over the weekend. It appears that pg_upgrade for 9.2b2 segfaults which more than likely has something to do with the resulting converted database appearing to have no rows. Earlier in this thread I reported that I was able to get the upgrade to work and this thread to be closed but I was in error. At the time I was also testing with the 9.1.4 pg_upgrade which does work and I thought that I had a successful 9.2b2 pg_upgrade run. Apologies for the confusion and let me know if you would like me to start a new thread. pg_upgrade 9.2b2 ... pg_toast.pg_toast_948075_index: 948081 to 948081 c0.page_metadata_values_pkey: 948082 to 948082 c0.i_page_metadata_values_short_name: 948084 to 948084 Segmentation Fault (core dumped) What is the actual program output that appears before the core dump message? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6733: All Tables Empty After pg_upgrade (PG 9.2.0 beta 2)
On Tue, Jul 17, 2012 at 04:01:08PM -0700, Mike Wilson wrote: Please find below the full pg_upgrade output. Let me know if there are any other questions that I may have missed. Note, the attached pg_upgrade stdout log is for the schema only pg_dumpall upgrade attempt from PG842 - PG920b2. -rw-r--r-- mwilson/staff 314381 2012-07-17 18:57 pg842_pg_upgrade.log FYI, I was supplied with an anonymous SQL dump of the database and was unable to reproduce the failure on Debian. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6733: All Tables Empty After pg_upgrade (PG 9.2.0 beta 2)
On Tue, Jul 17, 2012 at 05:41:05PM -0700, Mike Wilson wrote: Given the stack trace previously supplied and that the pg_upgrade from 9.1.4 does actually complete successfully can you recommend how I might continue to diagnose the issue myself? Since the 9.1.4 version of pg_upgrade did work I've started to look at the source for pg_upgrade in an attempt to see if the 9.1.4 version of the tool could be used instead of the 9.2.0 version. Due to the # of differences in the pg_upgrade source though a port seems unlikely. Can I get the git version string of the PG source you used for the version of the source that you tested with? I'd like to make sure that I'm using the same version as your successful test. Thanks. I am using git head for testing. Tom sees a few things odd in load_directory() that might be causing some problems on Solaris, and this is new code for 9.2 for Solaris, so that might explain it. I think we need Tom to finish and then if you can grab our git source and test that, it would be great! -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 #6733: All Tables Empty After pg_upgrade (PG 9.2.0 beta 2)
On Thu, Jul 12, 2012 at 10:16:18PM +, mfwil...@gmail.com wrote:
The following bug has been logged on the website:
Bug reference: 6733
Logged by: Mike Wilson
Email address: mfwil...@gmail.com
PostgreSQL version: 9.1.4
Operating system: Solaris 10
Description:
Filing this under PG914 since there isn't an option for PG9.2beta2.
Possibly this should be filed elsewhere? If so apologies in advance.
We are starting to prepare for the PG9.2 release so I have been performing
some test pg_upgrades on our existing PG8.4.2 cluster. We have a sizable
database and limited space so we have been testing upgrading our cluster
using --link mode of pg_upgrade. After performing an upgrade though the new
PG92 database has only empty tables.
Running this:
export NEWPG=/usr/postgres/postgresql-9.2.0_slony-2.1.1
export OLDPG=/usr/postgres/pg842_slony210
LD_LIBRARY_PATH=${NEWPG}/lib PATH=${NEWPG}/bin:${PATH} /usr/bin/time
${NEWPG}/bin/pg_upgrade --verbose --link \
--old-datadir=/opt/postgres/db/root/old
--new-datadir=/opt/postgres/db/root/new --old-bindir=${OLDPG}/bin/64/ \
--new-bindir=${NEWPG}/bin/ --old-port=5432 --new-port=5920 --user=postgres
This runs without errors and I am able to start the new upgraded PG92beta2
cluster. The schemas appear to be correct as well as the table definitions
and other schema objects. The tables themselves though are all empty. Not
sure what is going on
OK, I just tested an upgrade from 9.1.4 to 9.2 HEAD and it worked just
fine using the regression database as content. The big question is what
is different about your setup. Can you check the data files in
/data/base/db_oid and see if they are all zero length in the new
cluster? A simple 'ls -l' should show it:
$ pwd
/u/pg/data/base/16413
$ ls -l
-rw--- 1 postgres postgres 122880 Jul 12 18:39 11744
-rw--- 1 postgres postgres 24576 Jul 12 18:39 11744_fsm
-rw--- 1 postgres postgres 8192 Jul 12 18:39 11744_vm
-rw--- 1 postgres postgres 16384 Jul 12 18:39 11746
-rw--- 1 postgres postgres 24576 Jul 12 18:39 11746_fsm
-
These should _not_ be all zeros. Please let me know what you find.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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 #6733: All Tables Empty After pg_upgrade (PG 9.2.0 beta 2)
On Thu, Jul 12, 2012 at 06:44:06PM -0400, Tom Lane wrote: mfwil...@gmail.com writes: This runs without errors and I am able to start the new upgraded PG92beta2 cluster. The schemas appear to be correct as well as the table definitions and other schema objects. The tables themselves though are all empty. Hmm, maybe it's confused about XID past/future? Could we see the output of pg_controldata for both old and new clusters? Uh, I thought we only changfed the xlog stuff in 9.3, not 9.2, so I am confused what would have changed in that area. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
