[GENERAL] pg_upgrade: out of memory

[Carrington, Matthew (Produban)]

Hi,

I have attempted to upgrade my Postgres installation this morning from 9.0.1 to 
9.2.0 and it failed with an out of memory problem using pg_dumpall to dump the 
first database.

So after backing out the change and restarting level 9.0.1, I've done some 
basic investigation into the failure of the 9.2.0 pg_upgrade by checking output 
in pg_upgrade_utility.log ...

command: "/opt/serviceMonitoring/postgres/bin/pg_dumpall" --port 50432 
--username "postgres" --schema-only --binary-upgrade  -f 
pg_upgrade_dump_all.sql >> "pg_upgrade_utility.log" 2>&1
pg_dump: out of memory
pg_dumpall: pg_dump failed on database "cahoot_monitoring", exiting

... rerunning pg_dump_all using existing version 9.0.1 ...

"/opt/serviceMonitoring/postgres/bin/pg_dumpall" --port 65432 --username 
"postgres" --schema-only --binary-upgrade  -f pg_upgrade_dump_all.sql

... works fine.

Try with version 9.2.0 as per pg_upgrade ...

"/opt/serviceMonitoring/postgres_9.2/bin/pg_dumpall" --port 65432 --username 
"postgres" --schema-only --binary-upgrade  -f pg_upgrade_dump_all.sql

pg_dump: out of memory
pg_dumpall: pg_dump failed on database "cahoot_monitoring", exiting

... tail -3 pg_upgrade_dump_all.sql ...

\connect cahoot_monitoring

... no help there.

Try with version 9.1.5 as per pg_upgrade ...

cd /tmp
"/opt/serviceMonitoring/postgres_9.1/bin/pg_dumpall" --port 65432 --username 
"postgres" --schema-only --binary-upgrade  -f pg_upgrade_dump_all.sql

... works fine.

Has anyone else hit this problem ?

Matthew
Emails aren't always secure, and they may be intercepted or changed
after they've been sent. Produban doesn't accept liability if this
happens. If you think someone may have interfered with this email,
please get in touch with the sender another way. This message and any
documents attached to it do not create or change any contract unless
otherwise specifically stated. Any views or opinions contained in this
message are solely those of the author, and do not necessarily represent
those of Produban, unless otherwise specifically stated and the sender
is authorised to do so. Produban doesn't accept responsibility for
damage caused by any viruses contained in this email or its attachments.
Emails may be monitored. If you've received this email by mistake,
please let the sender know at once that it's gone to the wrong person
and then destroy it without copying, using, or telling anyone about its
contents. Produban Servicios Informaticos Generales, S.L. (UK Branch).
Registered office: Shenley Wood House, Chalkdell Drive, Shenley Wood,
Milton Keynes MK5 6LA. Branch registration number BR 008486.
Ref:[PDB#014]

Re: [GENERAL] RFE: Column aliases in WHERE clauses

[Chris Angelico]

On Wed, Sep 19, 2012 at 11:15 PM, David Johnston  wrote:
> I could maybe see something like the following having some value:
>
> SELECT inverse
> FROM data
> WHERE x<>0 AND inverse > .5
> MACRO inverse (1/x)
>

WITH macros AS (SELECT *,1/x AS inverse FROM data) SELECT inverse FROM
macros WHERE x<>0 AND inverse > .5

ChrisA


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Passing row set into PL/pgSQL function.

[Craig Ringer]

On 09/20/2012 05:37 AM, Lucas Clemente Vella wrote:

I am trying to write a generic "upsert" function in PL/pgSQL, in a way
that I can specify the table were I want to insert/update, the columns
whose values I want to specify, and the values to be inserted.


http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

--
Craig Ringer


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump, send/recv

[Craig Ringer]

On 09/20/2012 01:01 AM, Ivan Voras wrote:

Hello,

Actually I have sort of two questions rolled into one: when creating
custom data types, there's the option to implement *_send() and *_recv()
functions in addition to *_in() and *_out(); does pg_dump use them for
binary dumps, and, if not, what uses them? Are they only an optional
optimization for storing binary data in the database?


I don't know what *else* they're used for, but there's a binary wire 
protocol (albeit a rarely used one) that I'm pretty sure uses them.


--
Craig Ringer



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Getting a leading zero on negative intervals with to_char?

[Craig Ringer]

Hi all

I'm wondering if there's any way to convince `to_char` to add a leading 
zero to the hours in negative intervals. The current behaviour feels 
wrong, in that FMHH24:MM and HH24:MM produce the same output for 
negative intervals:


   regress=# WITH x(i) AS (VALUES (INTERVAL '9:00'),(INTERVAL
   '-9:00'),(INTERVAL '11:00'),(INTERVAL '-11:00'),(INTERVAL
   '101:00'),(INTERVAL '-101:00') )
   SELECT i as "interval", to_char(i,'HH24:MM') as "HH24:MM",
   to_char(i,'FMHH24:MM') AS "FMHH24:MM" FROM x;
  interval  | HH24:MM | FMHH24:MM
   +-+---
 09:00:00   | 09:00   | 9:00
 -09:00:00  | -9:00   | -9:00
 11:00:00   | 11:00   | 11:00
 -11:00:00  | -11:00  | -11:00
 101:00:00  | 101:00  | 101:00
 -101:00:00 | -101:00 | -101:00
   (6 rows)

I can't find any way to produce the output '-09:00' . There's no 
apparent way to add an additional width-specifier. HH24 is clearly not 
constrained to be 2 digits wide, since "-11" and "101" and "-101" are 
all output by "HH24". It seems like "-9" should be "-09" with the HH24 
specifier, and "-9" with the "FMHH24" specifier.


Opinions?

Unless I'm doing something woefully wrong, Oracle compatibility doesn't 
seem to be an issue because we format intervals wildly differently to 
Oracle anyway:


http://sqlfiddle.com/#!4/d41d8/2751 

and it looks like Oracle handling of intervals isn't much like Pg anyway:

http://stackoverflow.com/questions/970249/format-interval-with-to-char


Arose from trying to find a non-ugly solution to this SO post:

http://stackoverflow.com/questions/12335438/server-timezone-offset-value/12338490#12338490

--
Craig Ringer

[GENERAL] Passing row set into PL/pgSQL function.

[Lucas Clemente Vella]

I am trying to write a generic "upsert" function in PL/pgSQL, in a way
that I can specify the table were I want to insert/update, the columns
whose values I want to specify, and the values to be inserted.

So far I have come up with a solution whose signature is:

CREATE OR REPLACE FUNCTION upsert(IN tname text, IN cnames text[],
VARIADIC vals anyarray) RETURNS void

Whose tname is the table, cnames are the columns ans vals the values.
The problem I have is when I try to call the function: I can only pass
values of a previously defined type, like:

SELECT upsert('my_table', ARRAY['key', 'data'], (10,
'hello')::my_table, (20, 'world')::my_table);

Instead of:

SELECT upsert('my_table', ARRAY['key', 'data'], (10, 'hello'), (20, 'world'));

What gives me the error:

ERROR:  PL/pgSQL functions cannot accept type record[]

This later approach would be much preferable, since I don't always
want to specify the full table row, but just some fields, and I would
be able to specify the columns in any order I want (as given in
cnames). Since PL/pgSQL is unable to receive a record[] parameter, is
there any alternative for passing a set of arbitrary compound values?
Is there any way of passing a table, like "VALUES (10, 'hello'), (20,
'world')" or a CTE?

Could I use any other language that does not require superuser
privileges to be installed? PL/pgSQL is preferable due to
availability, but using another language would be OK.

-- 
Lucas Clemente Vella
lve...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Difference between ON and WHERE in JOINs

[Gavin Flower]

On 20/09/12 03:08, Merlin Moncure wrote:

On Tue, Sep 18, 2012 at 7:47 PM, David Johnston  wrote:

>On Sep 18, 2012, at 20:21, Jean-Christophe Boggio  
wrote:
>

>>I'm looking for an article that explains the difference between these 
constructs IN POSTGRESQL (the rules seem to differ from one DB to another) :
>>
>>SELECT A.*
>>FROM A
>>JOIN B ON a.id=b.id AND A.somefield='somevalue'
>>
>>and
>>
>>SELECT A.*
>>FROM A
>>JOIN B ON a.id=b.id
>>WHERE A.somefield='somevalue'
>>
>>
>>I have noticed big differences though I don't know the rules and I've been 
bitten several times recently. Time to learn.
>>
>>Thanks,
>>
>>JC
>>

>
>There is no difference in your example.  Conceptually though I suggest using 
only table-table conditions in an ON clause and placing any table-value conditions 
into the where.
>
>The main time you get differences is when you use OUTER JOIN constructions 
since the order of filtering can affect the final result.  With an inner join the 
order of evaluation doesn't matter since all valid results will have a record from 
both sides of the join.
>
>This really shouldn't be platform specific as it is the core of SQL standard.  If you 
want to actually show examples with "big differences" maybe someone can explain 
the reason.  Otherwise the documentation is excellent to explore what syntax is available in 
PostgreSQL.  The SELECT SQL command is the defining location.

Yeah.  This comes up most often with left joins.  It's the source of
the #1 bug I see in SQL -- it trips up even the experts sometimes.

SELECT * FROM foo LEFT JOIN BAR ON foo.id = bar.id WHERE bar.col = 'something';

By having the filtering in the where clause, the intended purpose of
the left join, to return every row of foo, is being defeated and the
join will behave like an inner join.  The right way to do it is:

SELECT * FROM foo LEFT JOIN BAR ON foo.id = bar.id AND bar.col = 'something';

The difference here is that the filtering is now happening at join
time where the left join semantics are playing: always return foo and
return bar rows if and only if the join condition is met.

merlin


-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 
To make changes to your subscription: 
http://www.postgresql.org/mailpref/pgsql-general

Respected Sir,

You example is obvious!


... to me, only after I had spent ten minutes looking at it!


Thanks,
Gavin

Re: [GENERAL] Difference between ON and WHERE in JOINs

[David Johnston]

Comments embedded between 

> 
> David,
> 
> Thanks for the verbose explanations, really useful.
> 
> However, in my case :
> - type is never NULL


Assuming you mean the "og.type" (i.e., the right-side of the join) even
though the column itself is defined NOT NULL if there are no matching
records supplied from that table the column still appears in the "joined
relation" and in that relation the system uses NULL to represent that no
record was provided from the corresponding table.


> - there are no lines from uidinoldgroups that match the "FROM ldap" join.
> 
> But I think I got it :
> 
> drop table if exists tmpA;
> drop table if exists tmpB;
> create temp table tmpA (name varchar);
> insert into tmpA values ('jack');
> insert into tmpA values ('joe');
> create temp table tmpB (name varchar, value int); insert into tmpB
> values('jack',10);
> 
> -- case (1)
> select a.name,COUNT(b.*)
> from tmpA a
> LEFT JOIN tmpB b ON a.name=b.name AND b.value>0 group by a.name
> 
> This gives :
> Jack 1
> Joe 0
> 
==

The only danger here is that count(b.*) evaluates to zero while all the
values in b.*are actually NULL.

Try playing with "ARRAY_AGG()" instead of "COUNT()" to get a better feel for
what is being returned.  Trying adding some more records to A and B then
run:

SELECT name, array_agg(b.value)
FROM tmpA a
LEFT JOIN tmpB b USING (name)
GROUP BY name

===
> But :
> 
> -- case (2)
> select a.name,COUNT(b.*)
> from tmpA a
> LEFT JOIN tmpB b ON a.name=b.name
> WHERE b.value>0
> group by a.name
> 
> gives :
> Jack 1
> 
> No mention of Joe.

Correct, because Joe does not have any records on (a LEFT JOIN B) with
"value > 0" which is mandatory.

Basically once you get to the WHERE clause you really do not have distinct
"a" and "b" tables but rather you operate on "a LEFT JOIN b" where any
columns supplied by "b" may be NULL/missing.

Again, the same does apply to INNER JOIN but because no NULLs can be
introduced by an INNER JOIN thinking of "a" and "b" as still being distinct
relations doesn't impact the outcome.

In other words:

SELECT ...
FROM (tmpA LEFT JOIN tmpB USING name) tmpAB

WHERE (tmpAB.value > 0) -- this is what you are really saying in case # 2;
and within tmpAB "value" can be NULL because of the LEFT JOIN

WHERE tmpB.value > 0 -- this is an error; relation tmpB is not visible at
this point
==
> 
> Though :
> 
> -- case (3)
> select a.name,COUNT(b.*)
> from tmpA a
> LEFT JOIN tmpB b ON a.name=b.name
> WHERE (b.value>0 or b.value is null)
> group by a.name
> 
> Brings back Joe. The WHERE clause is evaluated AFTER the JOIN.
> 
> A subtle concept difference that makes big differences in the results.
> 
> Many thanks for the enlightenment.
> 
> And also for making me look at CTE constructs which I did not know of.
> They make things much clearer :
> 
> with b2 as (
>select name,value
>from tmpB
>where value>0
> )
> SELECT a.name, count(b.*)
> FROM tmpA a
> LEFT JOIN b2 b ON a.name=b.name
> GROUP BY a.name
> 
> Have a nice day, you made mine rich !
> 

Glad I could be of assistance!

David J.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Difference between ON and WHERE in JOINs

[Jean-Christophe Boggio]

David,

Thanks for the verbose explanations, really useful.

However, in my case :
- type is never NULL
- there are no lines from uidinoldgroups that match the "FROM ldap" join.

But I think I got it :

drop table if exists tmpA;
drop table if exists tmpB;
create temp table tmpA (name varchar);
insert into tmpA values ('jack');
insert into tmpA values ('joe');
create temp table tmpB (name varchar, value int);
insert into tmpB values('jack',10);

-- case (1)
select a.name,COUNT(b.*)
from tmpA a
LEFT JOIN tmpB b ON a.name=b.name AND b.value>0
group by a.name

This gives :
Jack 1
Joe 0

But :

-- case (2)
select a.name,COUNT(b.*)
from tmpA a
LEFT JOIN tmpB b ON a.name=b.name
WHERE b.value>0
group by a.name

gives :
Jack 1

No mention of Joe.

Though :

-- case (3)
select a.name,COUNT(b.*)
from tmpA a
LEFT JOIN tmpB b ON a.name=b.name
WHERE (b.value>0 or b.value is null)
group by a.name

Brings back Joe. The WHERE clause is evaluated AFTER the JOIN.

A subtle concept difference that makes big differences in the results.

Many thanks for the enlightenment.

And also for making me look at CTE constructs which I did not know of. 
They make things much clearer :


with b2 as (
  select name,value
  from tmpB
  where value>0
)
SELECT a.name, count(b.*)
FROM tmpA a
LEFT JOIN b2 b ON a.name=b.name
GROUP BY a.name

Have a nice day, you made mine rich !


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Why csvlog logs contexts without leading tab?

[hubert depesz lubaczewski]

Had a simple function calling another function, and the final function
was doing raise log.

When I enabled normal stderr logging, with absurdly full
log_line_prefix, I got this:

#v+
a[psql] u[depesz] d[depesz] r[[local]] h[[local]] p[15444] t[2012-09-13 
21:49:37 CEST] m[2012-09-13 21:49:37.840 CEST] i[SELECT] e[0] 
c[505238d0.3c54] l[3] s[2012-09-13 21:49:36 CEST] v[2/2] x[0] LOG:  [logged 
line]
a[psql] u[depesz] d[depesz] r[[local]] h[[local]] p[15444] t[2012-09-13 
21:49:37 CEST] m[2012-09-13 21:49:37.840 CEST] i[SELECT] e[0] 
c[505238d0.3c54] l[4] s[2012-09-13 21:49:36 CEST] v[2/2] x[0] CONTEXT:  SQL 
statement "SELECT b()"
PL/pgSQL function a() line 1 at PERFORM
a[psql] u[depesz] d[depesz] r[[local]] h[[local]] p[15444] t[2012-09-13 
21:49:37 CEST] m[2012-09-13 21:49:37.840 CEST] i[SELECT] e[0] 
c[505238d0.3c54] l[5] s[2012-09-13 21:49:36 CEST] v[2/2] x[0] STATEMENT:  
select a();
a[psql] u[depesz] d[depesz] r[[local]] h[[local]] p[15444] t[2012-09-13 
21:49:37 CEST] m[2012-09-13 21:49:37.841 CEST] i[SELECT] e[0] 
c[505238d0.3c54] l[6] s[2012-09-13 21:49:36 CEST] v[2/0] x[0] LOG:  duration: 
1.662 ms  statement: select a();
#v-

same function call, with syslog:

#v+
Sep 13 21:53:31 h3po4 postgres[16156]: [4-1] a[psql] u[depesz] d[depesz] 
r[[local]] h[[local]] p[16156] t[2012-09-13 21:53:31 CEST] m[2012-09-13 
21:53:31.852 CEST] i[SELECT] e[0] c[505239bb.3f1c] l[3] s[2012-09-13 
21:53:31 CEST] v[2/4] x[0] LOG:  [logged line]
Sep 13 21:53:31 h3po4 postgres[16156]: [4-2] a[psql] u[depesz] d[depesz] 
r[[local]] h[[local]] p[16156] t[2012-09-13 21:53:31 CEST] m[2012-09-13 
21:53:31.852 CEST] i[SELECT] e[0] c[505239bb.3f1c] l[4] s[2012-09-13 
21:53:31 CEST] v[2/4] x[0] CONTEXT:  SQL statement "SELECT b()"
Sep 13 21:53:31 h3po4 postgres[16156]: [4-3] #011PL/pgSQL function a() line 1 
at PERFORM
Sep 13 21:53:31 h3po4 postgres[16156]: [4-4] a[psql] u[depesz] d[depesz] 
r[[local]] h[[local]] p[16156] t[2012-09-13 21:53:31 CEST] m[2012-09-13 
21:53:31.852 CEST] i[SELECT] e[0] c[505239bb.3f1c] l[5] s[2012-09-13 
21:53:31 CEST] v[2/4] x[0] STATEMENT:  select a();
Sep 13 21:53:31 h3po4 postgres[16156]: [5-1] a[psql] u[depesz] d[depesz] 
r[[local]] h[[local]] p[16156] t[2012-09-13 21:53:31 CEST] m[2012-09-13 
21:53:31.852 CEST] i[SELECT] e[0] c[505239bb.3f1c] l[6] s[2012-09-13 
21:53:31 CEST] v[2/0] x[0] LOG:  duration: 1.599 ms  statement: select a();
#v-

Please note that the 3rd line in both examples has leading tab - either
literal tab in case of stderr log, or #011 in case of syslog.

But with csvlog, I got something different:

#v+
2012-09-13 21:51:12.642 
CEST,"depesz","depesz",15673,"[local]",5052392f.3d39,3,"SELECT",2012-09-13 
21:51:11 CEST,2/2,0,LOG,0,"[logged line]","SQL statement ""SELECT b()""
PL/pgSQL function a() line 1 at PERFORM","select a();",,,"psql"
2012-09-13 21:51:12.642 
CEST,"depesz","depesz",15673,"[local]",5052392f.3d39,4,"SELECT",2012-09-13 
21:51:11 CEST,2/0,0,LOG,0,"duration: 1.561 ms  statement: select 
a();","psql"
#v-

There is no leading tab on the 2nd line. Why? Is it intentional, or just
an omission?

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.com/


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] foreign key from array element

[Rafal Pietrak]

On Tue, 2012-09-18 at 18:39 -0400, Tom Lane wrote:
> Rafal Pietrak  writes:
> > postmaster/postmaster.o: In function `PostmasterMain':^M
> > postmaster.c:(.text+0x4bc8): undefined reference to `optreset'^M
> > tcop/postgres.o: In function `process_postgres_switches':^M
> > postgres.c:(.text+0x1422): undefined reference to `optreset'^M
> > utils/misc/ps_status.o: In function `set_ps_display':^M
> > ps_status.c:(.text+0xd4): undefined reference to `setproctitle'^M
> > collect2: ld returned 1 exit status^M
> > make[2]: *** [postgres] Error 1^M
> 
> We've heard of this happening as a result of libedit pulling in libbsd,
> which provides (broken) versions of these symbols and thus confuses
> configure into thinking they're present.  The "-ledit" in your link line
> suggests that you are linking to libedit, but the rest of that is just
> guesswork.  I'd suggest installing readline to see if the problem goes
> away.
> 
> Here's a previous report:
> http://archives.postgresql.org/pgsql-general/2011-11/msg00790.php
> 
>   regards, tom lane

Thenx. The readline trail worked for me too, but in a somewhat different
way (I haven't read the full thread before trying):

1. in my case: this is debian-squeeze, not ubuntu; but this should not
really matter.

2. initially, I've pulled all the build dependencies for postgresql-8.4,
as they are in debian source package. This installed libedit-dev, and
consequently my initial failure.

3. so after having this libedit/readline hint, I've purged libedit-dev,
and installed readline6-dev instead (didn't have to build it from
sources).

4. this didn't help

5. but running ./configure --with-readline did. and the build ended up
cleanly.

What puzzles me, is that in "configure.in:675", the selection of libedit
v.s. readline is commented with a timemark dated 2004 - since then
things should have changed in those libs, shouldn't they? And then, the
comment proceeds a test which is only taken on win32 architecture, but
then follows libedit preference for everybody. Was this libedit/readline
issue just the case for win32? and is it still valid? 

-R



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] initial sync of multiple streaming slaves simultaneously

[Mike Roest]

Performance.

On our production DB the fast-archiver transfers the datadir in about half
as much time as basebackup.

And since this happens on every failover (since clearing the datadir and
resyncing as if from scratch also takes about half the time as a rsync of
an existing datadir)

--Mike


On Wed, Sep 19, 2012 at 1:34 PM, Lonni J Friedman wrote:

> Just curious, is there a reason why you can't use pg_basebackup ?
>
> On Wed, Sep 19, 2012 at 12:27 PM, Mike Roest 
> wrote:
> >
> >> Is there any hidden issue with this that we haven't seen.  Or does
> anyone
> >> have suggestions as to an alternate procedure that will allow 2 slaves
> to
> >> sync concurrently.
> >>
> > With some more testing I've done today I seem to have found an issue with
> > this procedure.
> > When the slave starts up after the sync It reaches what it thinks is a
> > consistent recovery point very fast based on the pg_stop_backup
> >
> > eg:
> > (from the recover script)
> > 2012-09-19 12:15:02: pgsql_start start
> > 2012-09-19 12:15:31: pg_start_backup
> > 2012-09-19 12:15:31: -
> > 2012-09-19 12:15:31: 61/3020
> > 2012-09-19 12:15:31: (1 row)
> > 2012-09-19 12:15:31:
> > 2012-09-19 12:15:32: NOTICE:  pg_stop_backup complete, all required WAL
> > segments have been archived
> > 2012-09-19 12:15:32: pg_stop_backup
> > 2012-09-19 12:15:32: 
> > 2012-09-19 12:15:32: 61/30D8
> > 2012-09-19 12:15:32: (1 row)
> > 2012-09-19 12:15:32:
> >
> > While the sync was running (but after the pg_stop_backup) I pushed a
> bunch
> > of traffic against the master server.  Which got me to a current xlog
> > location of
> > postgres=# select pg_current_xlog_location();
> >  pg_current_xlog_location
> > --
> >  61/6834C450
> > (1 row)
> >
> > The startup of the slave after the sync completed:
> > 2012-09-19 12:42:49.976 MDT [18791]: [1-1] LOG:  database system was
> > interrupted; last known up at 2012-09-19 12:15:31 MDT
> > 2012-09-19 12:42:49.976 MDT [18791]: [2-1] LOG:  creating missing WAL
> > directory "pg_xlog/archive_status"
> > 2012-09-19 12:42:50.143 MDT [18791]: [3-1] LOG:  entering standby mode
> > 2012-09-19 12:42:50.173 MDT [18792]: [1-1] LOG:  streaming replication
> > successfully connected to primary
> > 2012-09-19 12:42:50.487 MDT [18791]: [4-1] LOG:  redo starts at
> 61/3020
> > 2012-09-19 12:42:50.495 MDT [18791]: [5-1] LOG:  consistent recovery
> state
> > reached at 61/3100
> > 2012-09-19 12:42:50.495 MDT [18767]: [2-1] LOG:  database system is
> ready to
> > accept read only connections
> >
> > It shows the DB reached a consistent state as of 61/3100 which is
> well
> > behind the current location of the master (and the data files that were
> > synced over to the slave).  And monitoring the server showed the expected
> > slave delay that disappeared as the slave pulled and recovered from the
> WAL
> > files that go generated after the pg_stop_backup.
> >
> > But based on this it looks like this procedure would end up with a
> > indeterminate amount of time (based on how much traffic the master
> processed
> > while the slave was syncing) that the slave couldn't be trusted for fail
> > over or querying as the server is up and running but is not actually in a
> > consistent state.
> >
> > Thinking it through the more complicated script version of the 2 server
> > recovery (where first past the post to run start_backup or stop_backup)
> > would also have this issue (although our failover slave would always be
> the
> > one running stop backup as it syncs faster so at least it would be always
> > consistent but the DR would still have the problem)
>

Re: [GENERAL] initial sync of multiple streaming slaves simultaneously

[Lonni J Friedman]

Just curious, is there a reason why you can't use pg_basebackup ?

On Wed, Sep 19, 2012 at 12:27 PM, Mike Roest  wrote:
>
>> Is there any hidden issue with this that we haven't seen.  Or does anyone
>> have suggestions as to an alternate procedure that will allow 2 slaves to
>> sync concurrently.
>>
> With some more testing I've done today I seem to have found an issue with
> this procedure.
> When the slave starts up after the sync It reaches what it thinks is a
> consistent recovery point very fast based on the pg_stop_backup
>
> eg:
> (from the recover script)
> 2012-09-19 12:15:02: pgsql_start start
> 2012-09-19 12:15:31: pg_start_backup
> 2012-09-19 12:15:31: -
> 2012-09-19 12:15:31: 61/3020
> 2012-09-19 12:15:31: (1 row)
> 2012-09-19 12:15:31:
> 2012-09-19 12:15:32: NOTICE:  pg_stop_backup complete, all required WAL
> segments have been archived
> 2012-09-19 12:15:32: pg_stop_backup
> 2012-09-19 12:15:32: 
> 2012-09-19 12:15:32: 61/30D8
> 2012-09-19 12:15:32: (1 row)
> 2012-09-19 12:15:32:
>
> While the sync was running (but after the pg_stop_backup) I pushed a bunch
> of traffic against the master server.  Which got me to a current xlog
> location of
> postgres=# select pg_current_xlog_location();
>  pg_current_xlog_location
> --
>  61/6834C450
> (1 row)
>
> The startup of the slave after the sync completed:
> 2012-09-19 12:42:49.976 MDT [18791]: [1-1] LOG:  database system was
> interrupted; last known up at 2012-09-19 12:15:31 MDT
> 2012-09-19 12:42:49.976 MDT [18791]: [2-1] LOG:  creating missing WAL
> directory "pg_xlog/archive_status"
> 2012-09-19 12:42:50.143 MDT [18791]: [3-1] LOG:  entering standby mode
> 2012-09-19 12:42:50.173 MDT [18792]: [1-1] LOG:  streaming replication
> successfully connected to primary
> 2012-09-19 12:42:50.487 MDT [18791]: [4-1] LOG:  redo starts at 61/3020
> 2012-09-19 12:42:50.495 MDT [18791]: [5-1] LOG:  consistent recovery state
> reached at 61/3100
> 2012-09-19 12:42:50.495 MDT [18767]: [2-1] LOG:  database system is ready to
> accept read only connections
>
> It shows the DB reached a consistent state as of 61/3100 which is well
> behind the current location of the master (and the data files that were
> synced over to the slave).  And monitoring the server showed the expected
> slave delay that disappeared as the slave pulled and recovered from the WAL
> files that go generated after the pg_stop_backup.
>
> But based on this it looks like this procedure would end up with a
> indeterminate amount of time (based on how much traffic the master processed
> while the slave was syncing) that the slave couldn't be trusted for fail
> over or querying as the server is up and running but is not actually in a
> consistent state.
>
> Thinking it through the more complicated script version of the 2 server
> recovery (where first past the post to run start_backup or stop_backup)
> would also have this issue (although our failover slave would always be the
> one running stop backup as it syncs faster so at least it would be always
> consistent but the DR would still have the problem)


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] initial sync of multiple streaming slaves simultaneously

[Mike Roest]

> Is there any hidden issue with this that we haven't seen.  Or does anyone
> have suggestions as to an alternate procedure that will allow 2 slaves to
> sync concurrently.
>
> With some more testing I've done today I seem to have found an issue with
this procedure.
When the slave starts up after the sync It reaches what it thinks is a
consistent recovery point very fast based on the pg_stop_backup

eg:
(from the recover script)
2012-09-19 12:15:02: pgsql_start start
2012-09-19 12:15:31: pg_start_backup
2012-09-19 12:15:31: -
2012-09-19 12:15:31: 61/3020
2012-09-19 12:15:31: (1 row)
2012-09-19 12:15:31:
2012-09-19 12:15:32: NOTICE:  pg_stop_backup complete, all required WAL
segments have been archived
2012-09-19 12:15:32: pg_stop_backup
2012-09-19 12:15:32: 
2012-09-19 12:15:32: 61/30D8
2012-09-19 12:15:32: (1 row)
2012-09-19 12:15:32:

While the sync was running (but after the pg_stop_backup) I pushed a bunch
of traffic against the master server.  Which got me to a current xlog
location of
postgres=# select pg_current_xlog_location();
 pg_current_xlog_location
--
 61/6834C450
(1 row)

The startup of the slave after the sync completed:
2012-09-19 12:42:49.976 MDT [18791]: [1-1] LOG:  database system was
interrupted; last known up at 2012-09-19 12:15:31 MDT
2012-09-19 12:42:49.976 MDT [18791]: [2-1] LOG:  creating missing WAL
directory "pg_xlog/archive_status"
2012-09-19 12:42:50.143 MDT [18791]: [3-1] LOG:  entering standby mode
2012-09-19 12:42:50.173 MDT [18792]: [1-1] LOG:  streaming replication
successfully connected to primary
2012-09-19 12:42:50.487 MDT [18791]: [4-1] LOG:  redo starts at 61/3020
2012-09-19 12:42:50.495 MDT [18791]: [5-1] LOG:  consistent recovery state
reached at 61/3100
2012-09-19 12:42:50.495 MDT [18767]: [2-1] LOG:  database system is ready
to accept read only connections

It shows the DB reached a consistent state as of 61/3100 which is well
behind the current location of the master (and the data files that were
synced over to the slave).  And monitoring the server showed the expected
slave delay that disappeared as the slave pulled and recovered from the WAL
files that go generated after the pg_stop_backup.

But based on this it looks like this procedure would end up with a
indeterminate amount of time (based on how much traffic the master
processed while the slave was syncing) that the slave couldn't be trusted
for fail over or querying as the server is up and running but is not
actually in a consistent state.

Thinking it through the more complicated script version of the 2 server
recovery (where first past the post to run start_backup or stop_backup)
would also have this issue (although our failover slave would always be the
one running stop backup as it syncs faster so at least it would be always
consistent but the DR would still have the problem)

Re: [GENERAL] Difference between ON and WHERE in JOINs

[David Johnston]

> I have this query working :
> select profil,count(og.name)
> from ldap l
> left join uidinoldgroups ug on l.uid=ug.uid left join oldgroups og on
> og.id=ug.idgroupe and og.rne='0410030k' and og.type='g'
> where l.profilgeneral='P'
> and l.rne='0410030k'
> group by l.profil
> 
> But if I put :
> and og.rne='0410030k' and og.type='g'
> in the where part (what you suggested and what I did naturally), I get 0
> results which is quite confusing (because it's an outer join).
> 
> Also, if I replace these with full outer joins, I still get 0 results.
> 
> With my data, if I replace og.type='g' with og.type='m' I get MOST OF my
> resultats back (those where the count() returns more than zero). I know
this
> is specific to my data but I really don't get the behaviour.
> 
> Thanks for your help,
> 
> JC
> 

The query you wrote is equivalent to this:

SELECT profil, count(og.name)
FROM ldap l
LEFT JOIN ... ug ON (l.uid = ug.uid)
LEFT JOIN (SELECT * FROM ... WHERE rne = '...' AND type = '...') og ON
(og.id = ug.idgroupe)
WHERE l.rne = '' AND l.type=''

As soon as you start putting the right-hand tables in the outer-most where
clause you have to deal with the fact that the outer join can cause those
columns to be null.  If you compare those columns to a literal value then
you are saying that you don't want that column to be NULL and by extension
you really meant to use an inner join.

If you had put:

WHERE l.rne = '' AND l.type = '' AND (og.type = 'g' OR og.type IS NULL)

That would give you the behavior such that if og.type is a known value AND
it is not 'g' then the item should NOT be counted.  If the value is unknown
or 'g' then count it.

As for a rule-of-thumb I would avoid non-relational conditions in the ON
clause all-together and move them into a sub-query as I showed above.  The
WHERE clause is reserved for the left side of the join hierarchy by default.
The ON clause is reserved for inter-relation comparisons.  When cobbling
together multiple JOINs I would also suggest making use of liberal newlines
as well as parentheses.  Lastly knowing the correct answer is not that
common; just starting building up the query piece-by-piece and keep in mind
that you have to deal with the NULLs introduced by the OUTER JOIN.  How you
deal with them is query specific and cannot be "rule-of-thumbed".

Yes, this is all more verbose but you've now separated the relational filter
and the joining into the designated areas making interpreting the query
easier.  Throw in usage of CTE/WITH and the important portion of the query
can be made succinct by moving the sub-queries and filters to the top and
out of the way.  You can refactor, if necessary, when you are done.

David J.








-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_dump, send/recv

[Ivan Voras]

Hello,

Actually I have sort of two questions rolled into one: when creating
custom data types, there's the option to implement *_send() and *_recv()
functions in addition to *_in() and *_out(); does pg_dump use them for
binary dumps, and, if not, what uses them? Are they only an optional
optimization for storing binary data in the database?



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] initial sync of multiple streaming slaves simultaneously

[Mike Roest]

Our sync script is setup to fail if the pg_start_backup fails as if it
fails for some other reason the sync won't be valid as the backup_label
file will be missing so the slave won't have the correct location to
restart from.

Originally I had gone down the road of changing the sync script such that
if the pg_start_backup failed and the backup_label file existed it would
sync the backup_label right away so it could then do the sync.  It was also
setup so that if it didn't start the backup it wouldn't stop the backup.
 This however didn't work as if the DR starts the backup and begins it sync
first, and the local slave then goes to startup and the backup is already
in progress it would complete the sync faster then the DR and then try to
start up.  But the local slave would not come up into hot standby until the
stop_backup was executed (it came up but would never switch over to allow
readonly queries).

At that point I was going to change the script to basically be whoever got
to the point of needing to stop the backup first would call stop backup.
 But the new procedure of calling start and then stop right away seems
simpler (it makes the slave startup script simpler for sure).


On Wed, Sep 19, 2012 at 10:05 AM, Mike Roest wrote:

>
> Specifically what is the error?
>>
> psql (9.1.5)
> Type "help" for help.
>
> postgres=# select pg_start_backup('hotbackup',true);
>  pg_start_backup
> -
>  61/B20
> (1 row)
>
> postgres=# select pg_start_backup('hotbackup',true);
> ERROR:  a backup is already in progress
> HINT:  Run pg_stop_backup() and try again.
> postgres=#
>
>

Re: [GENERAL] initial sync of multiple streaming slaves simultaneously

[Mike Roest]

> Specifically what is the error?
>
psql (9.1.5)
Type "help" for help.

postgres=# select pg_start_backup('hotbackup',true);
 pg_start_backup
-
 61/B20
(1 row)

postgres=# select pg_start_backup('hotbackup',true);
ERROR:  a backup is already in progress
HINT:  Run pg_stop_backup() and try again.
postgres=#

Re: [GENERAL] initial sync of multiple streaming slaves simultaneously

[Lonni J Friedman]

On Wed, Sep 19, 2012 at 8:59 AM, Mike Roest  wrote:
> Hey Everyone,
> We currently have a 9.1.5 postgres cluster running using streaming
> replication.  We have 3 nodes right now
>
> 2 - local that are setup with pacemaker for a HA master/slave set failover
> cluster
> 1 - remote as a DR.
>
> Currently we're syncing with the pretty standard routine
>
> clear local datadir
> pg_start_backup
> sync datadir with fast-archiver (https://github.com/replicon/fast-archiver)
> pg_stop_backup
> start slave
>
> We use the streaming replication with wal_keep_segments set to 1000 to get
> the required WAL files to the slaves.
>
> With this procedure we can currently only sync one of the slaves at a time
> if we failover.  As when the second machine goes to start the sync it errors
> out cause trying to run pg_start_backup fails.

Specifically what is the error?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] initial sync of multiple streaming slaves simultaneously

[Mike Roest]

Hey Everyone,
We currently have a 9.1.5 postgres cluster running using streaming
replication.  We have 3 nodes right now

2 - local that are setup with pacemaker for a HA master/slave set failover
cluster
1 - remote as a DR.

Currently we're syncing with the pretty standard routine

clear local datadir
pg_start_backup
sync datadir with fast-archiver (https://github.com/replicon/fast-archiver)
pg_stop_backup
start slave

We use the streaming replication with wal_keep_segments set to 1000 to get
the required WAL files to the slaves.

With this procedure we can currently only sync one of the slaves at a time
if we failover.  As when the second machine goes to start the sync it
errors out cause trying to run pg_start_backup fails.

We're looking into was to allow both the slave and the DR to sync at the
same time.

The procedure I'm currently testing is

clear localdatadir
pg_start_backup
scp datadir/backuplabel
pg_stop_backup
sync datadir with fast-archiver
start slave

This seems to be working and the slave comes up correctly and streams the
WAL files it needs from the backup_label that was copied during the
pg_start_backup/pg_stop_backup

Is there any hidden issue with this that we haven't seen.  Or does anyone
have suggestions as to an alternate procedure that will allow 2 slaves to
sync concurrently.


Thanks

Re: [GENERAL] Difference between ON and WHERE in JOINs

[Kevin Grittner]

Merlin Moncure  wrote:
 
> By having the filtering in the where clause, the intended purpose
> of the left join, to return every row of foo, is being defeated
> and the join will behave like an inner join.  The right way to do
> it is:
> 
> SELECT * FROM foo LEFT JOIN BAR ON foo.id = bar.id AND bar.col = 
> 'something';
 
Consider the hypothetical case of a person table which is vertically
partitioned to have less frequently used information stored in a
demographics table.  The goal is to keep the person table narrow, so
that common uses (which don't reference any of the demographics
information) can be faster.  The demographics row is only present if
one or more of the non-key values is not null.  Let's say you want
to do a list which only includes people not known to be dead.  If
you put the `demographics.date_of_death IS NULL` test in the ON
clause, in a manner similar to your above example, you will get
incorrect results.
 
I will concede that most of the time you want conditions related to
the right-hand table of a left join in the ON clause; but that's not
always the right thing to do.  The question is: "Is this a condition
which should control whether data from the optional table is
included, or is this a condition which should determine whether the
joined data row as a whole is included in the result?"
 
-Kevin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Time-based trigger

[Robert Sosinski]

Good deduction Steve.

Looks like we are going to use the timestamp idea.  This way, the ticket will 
be open for sale again the second it's hold_until time lapses.  The cronjob was 
a close second, but there could be a lag-time between runs.

Thanks everyone for all the help. 

-- 
Robert Sosinski


On Tuesday, September 18, 2012 at 4:04 PM, Steve Crawford wrote:

> On 09/18/2012 08:59 AM, Robert Sosinski wrote:
> > We have a table, which has items that can be put on hold of 5 minutes 
> > (this is for an online store) once they are placed into a cart. What 
> > we need is for this hold to automatically expire after 5 minutes. 
> > Right now, we put a time stamp into the row (called hold_until) at 5 
> > minutes into the future, and select items where hold_until is less 
> > then now().
> > 
> > Would it be possible to change this to using a boolean that is set to 
> > true when item is put on hold, and have something like a time-based 
> > trigger automatically update the held boolean to false after 5 minutes 
> > pass.
> > 
> 
> I'm surmise by your domain that the items in question are not inventory 
> that you need to check against (reserved one of 15 lamps) but unique 
> individual items like event seats. While there aren't specifically 
> time-based triggers there are plenty of other options depending on the 
> nature of your queries.
> 
> There is a good possibility that the time column won't be used in 
> queries. If the items table is tickets for many events then an index on 
> the event will likely be used with the time column as a filter on the 
> index results. You may even be able to create a multi-column index that 
> will better restrict the results. Something like event/seat-category or 
> whatever fits your use-case. I'm sure that once an item is purchased it 
> is either removed or flagged in which case the event/available might be 
> a good index.
> 
> My first inclination would be to make the hold-till column "not-null 
> default now()" (or now() - '1 second'::interval if you prefer) which 
> would make your query work fine without additional null checking, would 
> work well as an indexed column if you need to see *all* reserved or 
> non-reserved items, and would not require any external cron-job cleaning 
> support.
> 
> Cheers,
> Steve
> 
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org 
> (mailto:pgsql-general@postgresql.org))
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 
> 

Re: [GENERAL] Difference between ON and WHERE in JOINs

[Jean-Christophe Boggio]

Le 19/09/2012 17:08, Merlin Moncure a écrit :

SELECT * FROM foo LEFT JOIN BAR ON foo.id = bar.id AND bar.col = 'something';

The difference here is that the filtering is now happening at join
time where the left join semantics are playing: always return foo and
return bar rows if and only if the join condition is met.


Ok, as a rule of thumb, should I put in the where clause only the 
conditions related to foo and inner joins ? All other (outer) conditions 
should then go on their respective 'ON' clauses ?


JC


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Difference between ON and WHERE in JOINs

[Jean-Christophe Boggio]

Le 19/09/2012 02:47, David Johnston a écrit :
> There is no difference in your example.  Conceptually though I
> suggest using only table-table conditions in an ON clause and placing
> any table-value conditions into the where.

This is how I use it usually.

> The main time you get differences is when you use OUTER JOIN
> constructions since the order of filtering can affect the final
> result.  With an inner join the order of evaluation doesn't matter
> since all valid results will have a record from both sides of the
> join.

Ok, I didn't know what the "trigger" was : outer joins, ok.

I have this query working :
select profil,count(og.name)
from ldap l
left join uidinoldgroups ug on l.uid=ug.uid
left join oldgroups og on og.id=ug.idgroupe and og.rne='0410030k' and 
og.type='g'
where l.profilgeneral='P'
and l.rne='0410030k'
group by l.profil

But if I put :
and og.rne='0410030k' and og.type='g'
in the where part (what you suggested and what I did naturally), I get 0 
results which is quite confusing (because it's an outer join).

Also, if I replace these with full outer joins, I still get 0 results.

With my data, if I replace og.type='g' with og.type='m' I get MOST OF my 
resultats back (those where the count() returns more than zero). I know this is 
specific to my data but I really don't get the behaviour.

Thanks for your help,

JC


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Difference between ON and WHERE in JOINs

[Merlin Moncure]

On Tue, Sep 18, 2012 at 7:47 PM, David Johnston  wrote:
> On Sep 18, 2012, at 20:21, Jean-Christophe Boggio  
> wrote:
>
>> I'm looking for an article that explains the difference between these 
>> constructs IN POSTGRESQL (the rules seem to differ from one DB to another) :
>>
>> SELECT A.*
>> FROM A
>> JOIN B ON a.id=b.id AND A.somefield='somevalue'
>>
>> and
>>
>> SELECT A.*
>> FROM A
>> JOIN B ON a.id=b.id
>> WHERE A.somefield='somevalue'
>>
>>
>> I have noticed big differences though I don't know the rules and I've been 
>> bitten several times recently. Time to learn.
>>
>> Thanks,
>>
>> JC
>>
>
> There is no difference in your example.  Conceptually though I suggest using 
> only table-table conditions in an ON clause and placing any table-value 
> conditions into the where.
>
> The main time you get differences is when you use OUTER JOIN constructions 
> since the order of filtering can affect the final result.  With an inner join 
> the order of evaluation doesn't matter since all valid results will have a 
> record from both sides of the join.
>
> This really shouldn't be platform specific as it is the core of SQL standard. 
>  If you want to actually show examples with "big differences" maybe someone 
> can explain the reason.  Otherwise the documentation is excellent to explore 
> what syntax is available in PostgreSQL.  The SELECT SQL command is the 
> defining location.

Yeah.  This comes up most often with left joins.  It's the source of
the #1 bug I see in SQL -- it trips up even the experts sometimes.

SELECT * FROM foo LEFT JOIN BAR ON foo.id = bar.id WHERE bar.col = 'something';

By having the filtering in the where clause, the intended purpose of
the left join, to return every row of foo, is being defeated and the
join will behave like an inner join.  The right way to do it is:

SELECT * FROM foo LEFT JOIN BAR ON foo.id = bar.id AND bar.col = 'something';

The difference here is that the filtering is now happening at join
time where the left join semantics are playing: always return foo and
return bar rows if and only if the join condition is met.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Change key primary for key foreign

[Gavin Flower]

On 19/09/12 19:40, Albe Laurenz wrote:

Guilherme Rodrigues wrote:

I created one table so:

CREATE TABLE clima (
 city char(80),
 cprc int,
);

And have other table so:

CREATE TABLE city (
 namechar(80),
 other_thing int,
);

These SQL statements have syntax errors (comma after the last column).


But now I want the table clima receive name table city as foreign key.

understand?

Sorry my bad english.

You will need a UNIQUE constraint on the referenced column.
Since there is no primary key yet, we'll define one:

ALTER TABLE city ADD CONSTRAINT city_pkey PRIMARY KEY (name);

Then you need a FOREIGN KEY constraint on table "clima":

ALTER TABLE clima ADD CONSTRAINT clima_city_fkey FOREIGN KEY (city)
REFERENCES city(name);

But really, you need much more.
First, for performance reasons it is highly advisable that
you define an index on clima(city).
Then you should habe a primary key on each table.
You can roll that into one:

ALTER TABLE clima ADD CONSTRAINT clima_pkey PRIMARY KEY (city);

But I think that using the city name as primary key is
not a good idea to begin with.  There could be different cities
with the same name, for one.  So in that case it would be best
to define an "artificial primary key column", some integer
like "city_id" and "clima_id".
Then introduce a column "clima.city_id" and define the foreign key
on that column.

You probably still have to learn a few things about
physical table design.  I suggest that you read what the PostgreSQL
manual has to say about data definition:
http://www.postgresql.org/docs/9.2/static/ddl.html

Yours,
Laurenz

For example: I know of 2 cities named London, one in England, the other 
in Canada.


Generally avoid primary keys that have real world significance as the 
outside world might change them, or the values may not be actually be 
unique.



Cheers,
Gavin


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Change key primary for key foreign

[Guilherme Rodrigues]

Em terça-feira, 18 de setembro de 2012 17h37min59s UTC-3, Guilherme Rodrigues  
escreveu:
> Hello. I want to change one table already created. Where the comand? thank 
> you.

Ok. Thank All.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] RFE: Column aliases in WHERE clauses

[David Johnston]

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Steve Haresnape
> Sent: Wednesday, September 19, 2012 2:37 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] RFE: Column aliases in WHERE clauses
> 
> Hi There,
> 
> I've snipped a piece for the daily digest because I take issue with what's
> asserted here as a reason for not allowing aliases in where clauses.
> 
> << snipThis isn't just academic nit-picking either, because the SELECT
> expressions might not be valid for rows that don't pass WHERE etc.
> Consider
>  SELECT 1/x AS inverse FROM data WHERE x <> 0; The implementation
> *must* apply WHERE before computing the SELECT
> expressions, or it'll get zero-divide failures that should not happen.
end
> snip>>
> 
> Irrespective of whether the standard prohibits aliases in where clauses,
the
> reasoning here is irrelevant to the discussion at hand.
> 
> If I say:
>  SELECT 1/x AS inverse FROM data WHERE x <> 0 or inverse > 0.5 (for
> arguments sake) (in a SQL dialect that supports it) then I must expect
inverse
> to be evaluated for every row, exactly as if I said:
>  SELECT 1/x AS inverse FROM data WHERE x <> 0 or 1/x > 0.5
> 
> It's surely not the role of the standard to protect us from the
consequences
> of our own folly.
> 
> Since some dialects support the idiom and others don't there can't be any
> compelling reason to withhold support. It's really a matter of style.
> For my money the DRY style is better.
> 

There are any number of idioms that other dialects (and even PostgreSQL)
support for compatibility or standards reasons that, if decided upon now,
would not be included.  

The SQL execution model is, from what I can infer, single-pass and linear
and while necessitating sometimes verbose syntax it makes execution
considerably less-problematic and more deterministic by the simple fact that
column names are more narrowly scoped.  While I can and have seen situations
where such a feature would be handy working around it is not that difficult.
For really complex expressions coding the formula into a (ideally immutable)
function is a better solution anyway.

As to Tom's example its main implication is that the WHERE-clause has to be
evaluated before the SELECT-list in the single-pass linear model.  That is
quite relevant if not the MAIN point of the example.

I could maybe see something like the following having some value:

SELECT inverse 
FROM data 
WHERE x<>0 AND inverse > .5 
MACRO inverse (1/x)

Apart from all this I'll simply say that because the feature itself has
value it is the means of implementation that needs to be discussed and not
the merits of the feature itself.  Since no new capabilities are being
added, just verbosity reduction, the hurdle to spend development time on
this is pretty darn high.  To phrase it differently I do not believe that
the core team would outright reject the idea of making aliases work IF they
were presented with a suitable implementation; it just sounds like they are
"withholding support" to the extent that they have not been convinced to do
the work themselves.  As an outsider I can understand, and in this case
agree with, that position.

David J.







-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] RFE: Column aliases in WHERE clauses

[Rafal Pietrak]

On Wed, 2012-09-19 at 20:36 +0800, Craig Ringer wrote:
> On 09/19/2012 02:36 PM, Steve Haresnape wrote:
> 
> > Since some dialects support the idiom and others don't there can't be
> > any compelling reason to withhold support. It's really a matter of style.
> > For my money the DRY style is better.
> 
> So you're saying that you want the implementation to effectively clone 
> the aliased SELECT term into the WHERE clause?
> 
> If so, what about functions with side-effects?

What about them: if they are put in the where clause by the user - e.g.
not from unaliasing?

Just bug-traceing will be more difficult from alias obfuscating effects.
That's all.

-R



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] drop table if exists ;

[Craig Ringer]

On 09/19/2012 07:54 PM, Marc Mamin wrote:

hello,

I've found a small logical issue while writing a migration script:

create table vtest (foo int);

create view vtest_v as select * from vtest;

drop table  if exists vtest_v;

ERROR:  "vtest_v" is not a table

drop view  if exists vtest;

ERROR:  "vtest" is not a view

this may be seen as a nice hint, but in my own opinion

DROP ... IF EXISTS should not throw an error for objects that do not exist.


... but they do exist. There is another object with that name. It isn't 
clear if the script author's intention is to DROP the object despite the 
type mismatch, or to ignore it because it's not the type of object they 
specified to drop.


When something is ambiguous or unclear, PostgreSQL will tend to report 
an error for safety.


--
Craig Ringer


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] RFE: Column aliases in WHERE clauses

[Craig Ringer]

On 09/19/2012 02:36 PM, Steve Haresnape wrote:


Since some dialects support the idiom and others don't there can't be
any compelling reason to withhold support. It's really a matter of style.
For my money the DRY style is better.


So you're saying that you want the implementation to effectively clone 
the aliased SELECT term into the WHERE clause?


If so, what about functions with side-effects?

--
Craig Ringer


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] drop table if exists ;

[Marc Mamin]

hello,

I've found a small logical issue while writing a migration script:

create table vtest (foo int);
create view vtest_v as select * from vtest;

drop table  if exists vtest_v;
ERROR:  "vtest_v" is not a table

drop view  if exists vtest;
ERROR:  "vtest" is not a view

this may be seen as a nice hint, but in my own opinion
DROP ... IF EXISTS should not throw an error for objects that do not
exist.
A warning would be better here.

This would allow such a code to be error proof:

...
drop table if exists foo;
drop view  if exists foo;
...

best regards,

Marc Mamin

Re: [GENERAL] Index creation takes more time?

[Herouth Maoz]

On 18/09/2012, at 20:19, Jeff Janes wrote:
> I think the one below will show an even larger discrepancy.  You are
> doing 2 casts for each comparison,
> so I think the casts overhead will dilute out the comparison.
> 
> select count(distinct foo) from  ( select cast(random() as varchar(14)) as foo
>   from generate_series (1,1)) asdf;

Actually, it doesn't. I suspect that it doesn't actually do string comparison 
per se. I don't know how "distinct" is implemented in PostgreSQL, but if it was 
me, I'd implement it with a hash table, which means that you calculate the hash 
of the string rather than compare it. Even if it is done with actual 
comparison, I don't think it's a collation-based comparison, but rather a 
byte-by-byte comparison.

> 
> 
>> Finally, I created a test table, as you asked:
>> 
>> 
>>> create table foo as select msisdn,sme_reference from
>>> sms.billing__archive limit 100;
>> 
>> Then I created an index on the msisdn and sme_reference columns together.
>> 99% of the data in the msisdn field consist of 11-digit phone numbers.
>> Result:
>> 
>> PC: 5792.641 ms
>> Server: 23740.470 ms
>> 
>> Huge discrepancy there.
> 
> try:
> create index ON foo (msisdn COLLATE "C", sme_reference) ;
> 
> This can only be done on 9.1 server, as that feature is new to that
> release.  It should be much faster to create than the index with
> default collation.
> 
> (or change the collation of msisdn column definition, rather than just
> in the index).
> 
> This assumes you just need the index for equality, not for some
> precise locale-specific ordering (which for phone numbers seems like a
> safe bet).


Yes, this certainly reduced the index creation time to within a reasonable 
margin. OK, now we have to decide whether to move the entire database to the 
'C' collation (which would require, I suppose, a dump and restore) with the 
option of changing collation for specific columns that actually need it, or to 
just solve the current problem by changing the index creation commands where 
relevant.

Thank you very much for your help with this issue, your input has been 
invaluable.

Herouth

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Column aliases in WHERE clauses

[Raymond O'Donnell]

On 19/09/2012 04:57, Chris Travers wrote:
> 
> Natural language semantics will get you into trouble though.  After all,
> I think Lisp follows natural language semantics remarkably closely if
> your natural language is Irish Gaelic

Really? I haven't used Irish seriously since I left school - maybe I
should learn Lisp :-)

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Change key primary for key foreign

[Albe Laurenz]

Guilherme Rodrigues wrote:
> I created one table so:
> 
> CREATE TABLE clima (
> city char(80),
> cprc int,
> );
> 
> And have other table so:
> 
> CREATE TABLE city (
> namechar(80),
> other_thing int,
> );

These SQL statements have syntax errors (comma after the last column).

> But now I want the table clima receive name table city as foreign key.
understand?
> Sorry my bad english.

You will need a UNIQUE constraint on the referenced column.
Since there is no primary key yet, we'll define one:

ALTER TABLE city ADD CONSTRAINT city_pkey PRIMARY KEY (name);

Then you need a FOREIGN KEY constraint on table "clima":

ALTER TABLE clima ADD CONSTRAINT clima_city_fkey FOREIGN KEY (city)
REFERENCES city(name);

But really, you need much more.
First, for performance reasons it is highly advisable that
you define an index on clima(city).
Then you should habe a primary key on each table.
You can roll that into one:

ALTER TABLE clima ADD CONSTRAINT clima_pkey PRIMARY KEY (city);

But I think that using the city name as primary key is
not a good idea to begin with.  There could be different cities
with the same name, for one.  So in that case it would be best
to define an "artificial primary key column", some integer
like "city_id" and "clima_id".
Then introduce a column "clima.city_id" and define the foreign key
on that column.

You probably still have to learn a few things about
physical table design.  I suggest that you read what the PostgreSQL
manual has to say about data definition:
http://www.postgresql.org/docs/9.2/static/ddl.html

Yours,
Laurenz Albe


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] foreign key from array element

[Gabriele Bartolini]

Ciao Rafal,

You can download the refreshed version of the patch: 
http://archives.postgresql.org/message-id/1347983571.11539.14.ca...@greygoo.devise-it.lan



5. where do I get current-v9.3 from?


git clone git://git.postgresql.org/git/postgresql.git
cd postgresql
git checkout -b aefk
bzcat Array-ELEMENT-foreign-key-v1-refreshed.patch.bz2 | patch -p1

Then regular configure and make procedures 
(http://www.postgresql.org/docs/current/interactive/install-procedure.html)


Useful link too: http://wiki.postgresql.org/wiki/Developer_FAQ

Hope this helps.

Thanks,
Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general