Re: [BUGS] BUG #5354: Type timestamptz doesn't allow to store time zone
Robert Haas wrote: > Vitali wrote: >> When I select from the table, the timetz has the correct time >> zone, the timestamptz has -6 as a time zone, which is my server >> default. > > I'm not sure what you think the bug is, but timestamptz definitely > doesn't store the time zone in which the value is input. What it > does is makes input and output relative to the then-current time > zones rather than fixed wall-clock times. We should probably add this to the FAQ -- the OP was expecting the behavior specified by the standard, in which TIMESTAMP WITH TIME ZONE includes a time zone. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] pg_dump -d
Hi, it seems pg_dump doesn't like -d option although it accepts --inserts. Same with -D and --column-inserts. I'm using pg_dump 8.4.1 on Ubuntu 9.10. I hope this is useful for you. Thank you very much, Vincent -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5357: PGAdmin: SQL Query Editor does not (always) open files
The following bug has been logged online: Bug reference: 5357 Logged by: Julien Email address: julien.bar...@gmail.com PostgreSQL version: 8.2.11 Operating system: Windows Description:PGAdmin: SQL Query Editor does not (always) open files Details: Reproduction scenario: 1- Open PGAdmin III(v1.8 or 1.10) and connect to a server 2- Click on the "SQL/pen" icon to launch the "Execute arbitraty SQL Queries" window 3- In this new window, choose File / Open and open a sql file (choose not to save the existing content of the window) In some cases (almost every time on my case) the file does not load and the windows still shows its previous content. This can occurs at the first load of a file, or more frequently when loading another SQL file. I tried PGAdmin III v1.8.4 and 1.10.1 and got the same problem. I then tried v1.6.3, and this time I did not get the error -- 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 #5357: PGAdmin: SQL Query Editor does not (always) open files
On Tue, Mar 02, 2010 at 10:08:55AM +, Julien wrote: > Description:PGAdmin: SQL Query Editor does not (always) open files A better place for pgadmin problems is the pgadmin-support list: http://www.pgadmin.org/support/list.php -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [BUGS] pg_dump -d
On Tue, Mar 02, 2010 at 11:09:49AM +, Vincent Maury wrote: > Hi, > > it seems pg_dump doesn't like -d option although it accepts --inserts. Same > with -D and --column-inserts. > I'm using pg_dump 8.4.1 on Ubuntu 9.10. I hope this is useful for you. > Thank you very much, > > Vincent pg_dump's options changed between 8.3 and 8.4, in order to help prevent potentially catastrophic slowdowns when people mistook the -d and -D options to mean "Connect to this database" rather than as equivalents of --inserts and --column-inserts. See the documentation for more details: http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html http://www.postgresql.org/docs/8.4/interactive/release-8-4.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [BUGS] BUG #5351: compiling with --disable-shared is broken (patch included)
Jonas Lund writes: > What kind of functionality is tied to shared libs ? Procedural languages, encoding conversions, walreceiver (in 9.0), not to mention any sort of add-on such as contrib or pgfoundry modules. > If building without shared libs isn't supported anymore then i think > you should disable it altogether in the configure script instead of > letting it be broken. +1 from my standpoint. If we did want to continue supporting it we'd need to think of a way to test it, and I'm not even sure what to test given the truncated functionality that would be available. 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
Re: [BUGS] BUG #5351: compiling with --disable-shared is broken (patch included)
> Hmm ... out of curiosity, what exactly is your motivation for doing > that? The reason we don't test this anymore is that so much of > Postgres' functionality is bound up in loadable modules that a build > without them seems of little use. It wouldn't even come close to > passing the regression tests, for example. Personally i got sick of having software break after having dependant libs changing their ABI's. (Say when having to upgrade some lib that is used by an service facing outwards also being used by some internal software). When i googled for the problem i also noticed that someone building iPhone software had the same problem as me. Apple doesn't allow dynamic libraries, and if you're developing anything seriously you also need to make a "fat" binary with arm code for the iPhone and x86 for the simulator. (I've worked with some iPhone software recently so it struck me eventhough i'd personally prolly go with sqlite for that) What kind of functionality is tied to shared libs ? If building without shared libs isn't supported anymore then i think you should disable it altogether in the configure script instead of letting it be broken. Having options that breaks the build isn't making anyone happy. > The reason I'm being picky about this is that the change in > Makefile.shlib was intentional, and I'm afraid that your change > would break other stuff that works now. Yes, it's a big possibility that it breaks something since it's a big block of things for a bunch of platforms and i was hesitant of submitting the patch initially for that reason. Then again.. 2 good reasons why it should go in or the entire thing should be changed back to as it was before (or have something better done by someone who knows the makefiles better than me). 1: This clause is only enabled with --disable-shared being specified and as far as i could spot and go through other makefiles i couldn't notice any of the rules being used outside unless shared libraries are used. 2: Shouldn't things mostly not having to do with non-shared libs go somewhere else than Makefile.shlib ? :) > I'm not even convinced > that it's a good way of solving your problem --- shouldn't the > all-shared-lib target not get built, if you've disabled shlib > building? That's the point of the patch however, the "all-shared-lib" rule is defined in this file as: all-shared-lib: $(shlib) and because you have a bunch of platform specific rules setting the shlib variable even if disable-shared is specified (and thus triggering the rule without building the lib) it cannot be set if this rule isn't to be activated. I'm not 100% familiar with how gmake works but i guess there would be 3 other ways to solve the issue with the last one probably being the most clean variant. 1: have an "ifeq ($(enable_shared), yes)" declaration around every definition of "shlib" in the platform specifics, this is bound to be broken over and over but for separate platforms. 2: "undefine" shlib after the platform specific section (does this work with gmake?) 3: make the all-shared-lib rule fully conditional or have an conditional variant without any dependancy (does this work with gmake?) regards, Jonas Lund -- 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 #5354: Type timestamptz doesn't allow to store time zone
Thank you for the response. I see your point. When I insert into field timezonetz it does convert into correct timezone and inserts the converted value. At the same time timestamp field just drops the timezone and inserts the value. However, I think that confusion comes when timetz works differently. Are there any plans to make them work the same either one way or another. Thanks, Vitali - Original Message - From: "Kevin Grittner" To: robertmh...@gmail.com, vit...@lumensoftware.com Cc: pgsql-bugs@postgresql.org Sent: Tuesday, March 2, 2010 7:35:32 AM Subject: Re: [BUGS] BUG #5354: Type timestamptz doesn't allow to store time zone Robert Haas wrote: > Vitali wrote: >> When I select from the table, the timetz has the correct time >> zone, the timestamptz has -6 as a time zone, which is my server >> default. > > I'm not sure what you think the bug is, but timestamptz definitely > doesn't store the time zone in which the value is input. What it > does is makes input and output relative to the then-current time > zones rather than fixed wall-clock times. We should probably add this to the FAQ -- the OP was expecting the behavior specified by the standard, in which TIMESTAMP WITH TIME ZONE includes a time zone. -Kevin
Re: [BUGS] BUG #5357: PGAdmin: SQL Query Editor does not (always) open files
On Tue, 2010-03-02 at 08:32 -0700, Joshua Tolley wrote: > On Tue, Mar 02, 2010 at 10:08:55AM +, Julien wrote: > > Description:PGAdmin: SQL Query Editor does not (always) open files > > A better place for pgadmin problems is the pgadmin-support list: > http://www.pgadmin.org/support/list.php I think we need to work on a distribution mechanism for such requests, rather than trouble bug reporters with our bureaucracy. pgAdmin has been bundled with Postgres for some time now. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5358: Throwing unexpected ERROR
The following bug has been logged online:
Bug reference: 5358
Logged by: Gurjeet Singh
Email address: singh.gurj...@gmail.com
PostgreSQL version: 8.4.2
Operating system: Windows Vista 64bit
Description:Throwing unexpected ERROR
Details:
I am using Postgres Plus Standard Server
version: PostgreSQL 8.4.2, compiled by Visual C++ build 1400, 32-bit
create table public.test( a bytea, c text )
select relname, pg_relation_size( 'public."' || relname || '"' )/1024
from (select distinct relname
from (select r.relname, c.attname, c.attnotnull, t.typname
from pg_namespace as s, pg_class as r, pg_attribute as c,
pg_type as t
where s.oid = r.relnamespace
and r.oid = c.attrelid
and c.atttypid = t.oid
and s.nspname = 'public'
and t.typname in ('bytea', 'text') ) as s1
) as s2
where pg_relation_size( 'public."' || relname || '"' ) <> 0;
ERROR: relation "public.pg_type" does not exist
** Error **
ERROR: relation "public.pg_type" does not exist
SQL state: 42P01
When I comment out the last WHERE clause, the query runs fine.
It seems that the WHERE predicate is being pushed into the subqueries a bit
too soon.
Here's the EXPLAIN:
Subquery Scan s2 (cost=123.50..124.23 rows=21 width=64)
-> HashAggregate (cost=123.50..123.71 rows=21 width=64)
-> Nested Loop (cost=10.93..123.44 rows=21 width=64)
Join Filter: (r.relnamespace = s.oid)
-> Seq Scan on pg_namespace s (cost=0.00..1.08 rows=1
width=4)
Filter: (nspname = 'public'::name)
-> Nested Loop (cost=10.93..121.03 rows=107 width=68)
-> Hash Join (cost=10.93..82.03 rows=107 width=4)
Hash Cond: (c.atttypid = t.oid)
-> Seq Scan on pg_attribute c (cost=0.00..61.57
rows=2257 width=8)
-> Hash (cost=10.90..10.90 rows=2 width=4)
-> Seq Scan on pg_type t (cost=0.00..10.90
rows=2 width=4)
Filter: (typname = ANY
('{bytea,text}'::name[]))
-> Index Scan using pg_class_oid_index on pg_class r
(cost=0.00..0.35 rows=1 width=72)
Index Cond: (r.oid = c.attrelid)
Filter: (pg_relation_size'public."'::text ||
(r.relname)::text) || '"'::text))::regclass, 'main'::text) <> 0)
Best regards,
--
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] possible bug not in open items
Tom Lane wrote: > Jeff Davis writes: > > On Thu, 2010-02-25 at 23:15 -0500, Bruce Momjian wrote: > >> Was this ever addressed? > > > It doesn't appear to be fixed, and I don't see it on the TODO, either. > > Should we add it there? > > +1. It likely wouldn't be real hard to fix, but given the lack of field > complaints I'm not thinking we need to treat it as urgent. Added to TODO: Allow a stalled COPY to exit if the backend is terminated * http://archives.postgresql.org/pgsql-bugs/2009-04/msg00067.php -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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 #5354: Type timestamptz doesn't allow to store time zone
Vitali Malinouski wrote: > Thank you for the response. I see your point. When I insert into > field timezonetz it does convert into correct timezone and > inserts the converted value. At the same time timestamp field > just drops the timezone and inserts the value. > > However, I think that confusion comes when timetz works differently. > Are there any plans to make them work the same either one way > or another. I think we are following the specification on that one so I don't see how we can change it. --- > - Original Message - From: "Kevin Grittner" > To: robertmh...@gmail.com, > vit...@lumensoftware.com Cc: pgsql-bugs@postgresql.org Sent: > Tuesday, March 2, 2010 7:35:32 AM Subject: Re: [BUGS] BUG #5354: > Type timestamptz doesn't allow to store time zone > > Robert Haas wrote: > > Vitali wrote: > > >> When I select from the table, the timetz has the correct time > >> zone, the timestamptz has -6 as a time zone, which is my server > >> default. > > > > I'm not sure what you think the bug is, but timestamptz definitely > > doesn't store the time zone in which the value is input. What it > > does is makes input and output relative to the then-current time > > zones rather than fixed wall-clock times. > > We should probably add this to the FAQ -- the OP was expecting > the behavior specified by the standard, in which TIMESTAMP WITH > TIME ZONE includes a time zone. > > -Kevin > -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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 #5358: Throwing unexpected ERROR
"Gurjeet Singh" writes:
> select relname, pg_relation_size( 'public."' || relname || '"' )/1024
> from (select distinct relname
> from (select r.relname, c.attname, c.attnotnull, t.typname
> from pg_namespace as s, pg_class as r, pg_attribute as c,
> pg_type as t
> where s.oid = r.relnamespace
> and r.oid = c.attrelid
> and c.atttypid = t.oid
> and s.nspname = 'public'
> and t.typname in ('bytea', 'text') ) as s1
> ) as s2
> where pg_relation_size( 'public."' || relname || '"' ) <> 0;
> ERROR: relation "public.pg_type" does not exist
That approach to generating a textual name for a relation is really
pretty unworkable: it's on the hairy edge of being vulnerable to
SQL injection attacks, not to mention being inefficient and unwieldy.
Just pass r.oid to pg_relation_size, instead.
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
Re: [BUGS] BUG #5351: compiling with --disable-shared is broken (patch included)
Jonas Lund wrote: >> Hmm ... out of curiosity, what exactly is your motivation for doing >> that? The reason we don't test this anymore is that so much of >> Postgres' functionality is bound up in loadable modules that a build >> without them seems of little use. It wouldn't even come close to >> passing the regression tests, for example. > > Personally i got sick of having software break after having dependant > libs changing their ABI's. (Say when having to upgrade some lib that > is used by an service facing outwards also being used by some internal > software). This doesn't force you to use static libraries. All reasonably modern platforms provide mechanisms to force preferential loading of particular shared libraries you've provided. On any modern UNIX, use rpath linking. For Linux, see the "ld.so" man page, particularly the $ORIGIN expansion token. On Windows, put the shared libraries in the same directory as the executable that's loading them, or use WinSxS linking with DLL manifests. On Mac OS X, use rpath linking, using install_name_tool to change path references from absolute paths to relative paths inside the application bundle. Seriously, static libraries are more pain than they're worth these days and you have as good or better options with shared library builds. > When i googled for the problem i also noticed that someone building > iPhone software had the same problem as me. Apple doesn't allow > dynamic libraries I think you must be referring to the fact that iPhone apps aren't allowed to install dynamically loaded libraries into shared locations where multiple apps can see them, or the fact that iPhone apps aren't supposed to dlopen(...) libraries. There should be no issue with including shared libraries in your application bundle. > What kind of functionality is tied to shared libs ? The server is built around shared library modules. The client library, libpq, can AFAIK be built statically if you really must, and shouldn't require shared libraries for anything. The client library is the only thing that'd make even a remote amount of sense to use on a device like the iPhone - trying to use the Pg server on the iPhone is men-in-white-coats crazy. > 1: This clause is only enabled with --disable-shared being specified > and as far as i could spot and go through other makefiles i couldn't > notice any of the rules being used outside unless shared libraries are > used. Perhaps offering an option to build *just* libpq, and do that statically, would be useful to some. -- Craig Ringer -- 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 #5358: Throwing unexpected ERROR
On Tue, Mar 2, 2010 at 7:51 PM, Tom Lane wrote:
> "Gurjeet Singh" writes:
> > select relname, pg_relation_size( 'public."' || relname || '"' )/1024
> > from (select distinct relname
> > from (select r.relname, c.attname, c.attnotnull, t.typname
> > from pg_namespace as s, pg_class as r, pg_attribute as c,
> pg_type as t
> > where s.oid = r.relnamespace
> > and r.oid = c.attrelid
> > and c.atttypid = t.oid
> > and s.nspname = 'public'
> > and t.typname in ('bytea', 'text') ) as s1
> > ) as s2
> > where pg_relation_size( 'public."' || relname || '"' ) <> 0;
>
> > ERROR: relation "public.pg_type" does not exist
>
> That approach to generating a textual name for a relation is really
> pretty unworkable: it's on the hairy edge of being vulnerable to
> SQL injection attacks, not to mention being inefficient and unwieldy.
> Just pass r.oid to pg_relation_size, instead.
>
I have gotten on to that path already, thanks for the advice.
This query will never be used by an application, so no fear of SQL injection
there. I was in the middle of a migration effort when I brewed this query.
The main inner query is what I started with to migrate only specific tables,
and the started slapping on outer queries to monitor the amount of data
already transferred. So I was rather surprised to see this error at a stage
where I did not expect it to fail.
IMHO the outer-most WHERE clause is being pushed through the subqueries when
it should not be. I tried to stop the optimizer from doing that and it seems
putting a LIMIT clause on S1 subquery make Postgres happy.
select relname, pg_relation_size( 'public."' || relname || '"' )/1024
from (select distinct relname
from (select r.relname, c.attname, c.attnotnull, t.typname
from pg_namespace as s, pg_class as r, pg_attribute as c,
pg_type as t
where s.oid = r.relnamespace
and r.oid = c.attrelid
and c.atttypid = t.oid
and s.nspname = 'public'
and t.typname in ('bytea', 'text') ) as s1 limit 1000
) as s2
where pg_relation_size( 'public."' || relname || '"' ) <> 0 ;
>From SQL perspective there should be no difference between this query and
the one in the first post since there's only one qualifying record.
Predicate push-down is definitely a good optimization, but it should not
affect the result-set. I have no idea how to tell optimizer to stop such
push-downs.
I am leaning towards marking this as a bug.
Best regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
