[GENERAL] statically compiling postgres and problem with initdb

[mona attariyan]

Hi,I statically compiled my postgres and I got the following message when I ran 
initdb:
creating conversions ... FATAL:  could not load library 
"/usr/local/pgsql/lib/ascii_and_mic.so": /usr/local/pgsql/lib/ascii_and_mic.so: 
undefined symbol: pg_ascii2micSTATEMENT:  CREATE OR REPLACE FUNCTION 
ascii_to_mic (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS 
'$libdir/ascii_and_mic', 'ascii_to_mic' LANGUAGE C STRICT;
There was a discussion about this in the archive, but there wasn't any fix for 
it. I have to compile postgres statically for the project I'm doing. Any 
suggestions on how to get around this problem?
Thanks--Mona

Re: [GENERAL] How to create "auto-increment" field WITHOUT a sequence object?

[Dmitriy Igrishin]

Hey Chris,

The suggestion of using for
> update is a good one, but it doesn't entirely get rid of the problem,
> which is inherent in ensuring gapless numbering in a system with
> concurrent transactions.
>
Why not?

I mean the following solution:

CREATE TABLE myseq(tabnm text not null, lastid integer not null);

INSERT INTO myseq SELECT 'mytab', 0; -- initialization

CREATE OR REPLACE FUNCTION public.myseq_nextval(a_tabnm text)
 RETURNS integer
 LANGUAGE sql
 STRICT
AS $function$
UPDATE myseq SET lastid = li + 1 FROM
  (SELECT lastid li FROM myseq WHERE tabnm = $1 FOR UPDATE) foo
  RETURNING lastid;
$function$

-- Test

dmitigr=> BEGIN;
BEGIN
dmitigr=> SELECT myseq_nextval('mytab');
 myseq_nextval
---
 1
(1 row)

dmitigr=> ROLLBACK;
ROLLBACK
dmitigr=> SELECT * FROM myseq;
 tabnm | lastid
---+
 mytab |  0
(1 row)

So, with this approach you'll get a lock only on INSERT.

dmitigr=> CREATE TABLE mytab(id integer not null DEFAULT
myseq_nextval('mytab'));
CREATE TABLE
dmitigr=> INSERT INTO mytab DEFAULT VALUES;
INSERT 0 1
dmitigr=> INSERT INTO mytab DEFAULT VALUES;
INSERT 0 1
dmitigr=> SELECT * FROM mytab;
 id

  1
  2
(2 rows)


-- 
// Dmitriy.

Re: [GENERAL] How to create "auto-increment" field WITHOUT a sequence object?

[Chris Travers]

On Fri, Jul 1, 2011 at 1:16 AM, Dmitriy Igrishin  wrote:
> Hey Chris,
>
>> The suggestion of using for
>> update is a good one, but it doesn't entirely get rid of the problem,
>> which is inherent in ensuring gapless numbering in a system with
>> concurrent transactions.
>
> Why not?

Just because it locks less doesn't mean that it doesn't lock.

The point is:  if gaps are acceptable then the sequences which exist
outside of transactions are idea.  If gaps are not acceptable, you
have to lock and force transactions through the system serially which
means a possibility of deadlocks and performance issues.  These issues
are inherent in gapless numbering because you can't get a gapless
sequence when things roll back without such locks.
>
> I mean the following solution:
>
> CREATE TABLE myseq(tabnm text not null, lastid integer not null);
>
> INSERT INTO myseq SELECT 'mytab', 0; -- initialization
>
> CREATE OR REPLACE FUNCTION public.myseq_nextval(a_tabnm text)
>  RETURNS integer
>  LANGUAGE sql
>  STRICT
> AS $function$
> UPDATE myseq SET lastid = li + 1 FROM
>   (SELECT lastid li FROM myseq WHERE tabnm = $1 FOR UPDATE) foo
>   RETURNING lastid;
> $function$
>
> -- Test
>
> dmitigr=> BEGIN;
> BEGIN
> dmitigr=> SELECT myseq_nextval('mytab');
>  myseq_nextval
> ---
>  1
> (1 row)
>
> dmitigr=> ROLLBACK;
> ROLLBACK
> dmitigr=> SELECT * FROM myseq;
>  tabnm | lastid
> ---+
>  mytab |  0
> (1 row)
>
> So, with this approach you'll get a lock only on INSERT.

True.  But the point us that you MUST lock on insert to get gapless
sequences, and this creates inherent problems in terms of performance
and concurrency, so that you should not use it unless you really have
no other choice (i.e. because the tax authorities demand it).

Best Wishes,
Chris Travers

-- 
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] statically compiling postgres and problem with initdb

[Craig Ringer]

On 1/07/2011 3:28 PM, mona attariyan wrote:

Hi,
I statically compiled my postgres and I got the following message when I
ran initdb:


How did you compile Pg statically?

Please show your configure command line, any environment variables you 
set, etc.


You've also left out a huge amount of information, like your Pg version, 
where you got the sources from, your OS version, your compiler version, 
etc. I strongly suggest that you read this:


  http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

before posting a follow-up.


There was a discussion about this in the archive, but there wasn't any
fix for it. I have to compile postgres statically for the project I'm
doing.


Why? Perhaps if you explained that in a bit more detail it'd be possible 
to find an alternative.


--
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] How to create "auto-increment" field WITHOUT a sequence object?

[Dmitriy Igrishin]

2011/7/1 Chris Travers 

> On Fri, Jul 1, 2011 at 1:16 AM, Dmitriy Igrishin 
> wrote:
> > Hey Chris,
> >
> >> The suggestion of using for
> >> update is a good one, but it doesn't entirely get rid of the problem,
> >> which is inherent in ensuring gapless numbering in a system with
> >> concurrent transactions.
> >
> > Why not?
>
> Just because it locks less doesn't mean that it doesn't lock.
>
> The point is:  if gaps are acceptable then the sequences which exist
> outside of transactions are idea.  If gaps are not acceptable, you
> have to lock and force transactions through the system serially which
> means a possibility of deadlocks and performance issues.  These issues
> are inherent in gapless numbering because you can't get a gapless
> sequence when things roll back without such locks.
>
Then I don't clearly understand the existence of locks (the LOCK
command, SELECT FOR UPDATE clause and so on) if the usage
of them gives only problems...

>
> > I mean the following solution:
> >
> > CREATE TABLE myseq(tabnm text not null, lastid integer not null);
> >
> > INSERT INTO myseq SELECT 'mytab', 0; -- initialization
> >
> > CREATE OR REPLACE FUNCTION public.myseq_nextval(a_tabnm text)
> >  RETURNS integer
> >  LANGUAGE sql
> >  STRICT
> > AS $function$
> > UPDATE myseq SET lastid = li + 1 FROM
> >   (SELECT lastid li FROM myseq WHERE tabnm = $1 FOR UPDATE) foo
> >   RETURNING lastid;
> > $function$
> >
> > -- Test
> >
> > dmitigr=> BEGIN;
> > BEGIN
> > dmitigr=> SELECT myseq_nextval('mytab');
> >  myseq_nextval
> > ---
> >  1
> > (1 row)
> >
> > dmitigr=> ROLLBACK;
> > ROLLBACK
> > dmitigr=> SELECT * FROM myseq;
> >  tabnm | lastid
> > ---+
> >  mytab |  0
> > (1 row)
> >
> > So, with this approach you'll get a lock only on INSERT.
>
> True.  But the point us that you MUST lock on insert to get gapless
> sequences, and this creates inherent problems in terms of performance
> and concurrency, so that you should not use it unless you really have
> no other choice (i.e. because the tax authorities demand it).
>
Sure, but, again, why LOCK and SELECT FOR UPDATE exists ?

>
> Best Wishes,
> Chris Travers
>



-- 
// Dmitriy.

Re: [GENERAL] How to create "auto-increment" field WITHOUT a sequence object?

[Craig Ringer]

On 1/07/2011 4:21 PM, Chris Travers wrote:


means a possibility of deadlocks and performance issues.  These issues
are inherent in gapless numbering because you can't get a gapless
sequence when things roll back without such locks.


Actually, another approach that allows parallel transactions is (at 
least theoretically) possible. You can hand out IDs as transactions 
request them, and if a transaction rolls back you can abort it *and* 
*all* *transactions* *given* *higher* *IDs*, then re-issue the lot. I 
guess that could be useful if transaction failure was extremely unlikely 
and you needed to have lots of work in flight at once.


In practice I don't think it'd be very useful to do this, because 
transactions would still have to commit in the order they obtained IDs 
in, so a slow or blocked transaction would still stall the system just 
like it does with lock-based gapless numbering. Also, once a later 
transaction had obtained an ID prior transactions couldn't obtain any 
more IDs.


I'm not sure this is possible in Pg without modifying the server, 
either. It'd be kind of interesting in a useless-toy-project kind of way.


--
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] Anonymous record type and inner types.

[Ronan Dunklau]

Hello.

Sorry if this question has already been asked, but I couldn't find the answer 
anywhere.

When I execute a statement such as "Select (an_int, a_varchar) as a_record 
from test, the type returned for a_record is "record" (oid 2249).

Is the "inner type" information propagated somehow (that an_int is an integer, 
and a_varchar a varchar) ?

If so, is it easily accessible in client libraries ? I'm using psycopg2 for 
python, which itself uses libpq.

Thanks.

Regards,

--
Ronan Dunklau








signature.asc
Description: This is a digitally signed message part.

[GENERAL] Anonymous record type and inner types.

[rdunklau]

This message has been digitally signed by the sender.

_GENERAL__Anonymous_record_type_and_inner_types_.eml
Description: Binary data


-
Hi-Tech Gears Ltd, Gurgaon, India


-- 
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] statically compiling postgres and problem with initdb

[mona attariyan]

Sorry about the incomplete question. I'm compiling postgres 9.0.4 from source 
code and I got the tar ball from 
here: http://www.postgresql.org/ftp/source/v9.0.4/
I'm using Postgres to evaluate a research tool and the tool doesn't work with 
dynamic libraries. That's why I need to compile it statically. I also have a 
custom version of glibc which is slightly different from glibc 2.5.1. The 
difference is related to the use of hardware counters for network related libc 
functions. Shouldn't matter for Postgres compilation. Here is what I pass to 
configure (I try to compile statically with my custom glibc):
CFLAGS="-O0 -I/[PATH]/glibc-2.5.1-custom/prefix/include -static -pthread 
$CFLAGS" \CPPFLAGS="-O0 -I/[PATH]/glibc-2.5.1-custom/prefix/include -static 
-pthread $CPPFLAGS" \LDFLAGS="-static 
-Wl,-rpath,/[PATH]/glibc-2.5.1-custom/prefix/lib 
-L/[PATH]/glibc-2.5.1-custom/prefix/lib -Wl,-z,now 
-Wl,--dynamic-linker=/[PATH]/glibc-2.5.1-custom/prefix/lib/ld-linux.so.2 
$LDFLAGS" \./configure --without-readline
and I'm compiling on a Fedora 7 on a custom kernel which is close to 2.6.26.The 
compilation finished fine and it installed correctly. When I try to run initdb 
I get the following message:
creating conversions ... FATAL:  could not load library 
"/usr/local/pgsql/lib/ascii_and_mic.so": /usr/local/pgsql/lib/ascii_and_mic.so: 
undefined symbol: pg_ascii2micSTATEMENT:  CREATE OR REPLACE FUNCTION 
ascii_to_mic (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS 
'$libdir/ascii_and_mic', 'ascii_to_mic' LANGUAGE C STRICT;
Thanks--Mona
--- On Fri, 7/1/11, Craig Ringer  wrote:

From: Craig Ringer 
Subject: Re: [GENERAL] statically compiling postgres and problem with initdb
To: pgsql-general@postgresql.org, mona_attar...@yahoo.com
Date: Friday, July 1, 2011, 1:26 AM

On 1/07/2011 3:28 PM, mona attariyan wrote:
> Hi,
> I statically compiled my postgres and I got the following message when I
> ran initdb:

How did you compile Pg statically?

Please show your configure command line, any environment variables you set, etc.

You've also left out a huge amount of information, like your Pg version, where 
you got the sources from, your OS version, your compiler version, etc. I 
strongly suggest that you read this:

  http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

before posting a follow-up.

> There was a discussion about this in the archive, but there wasn't any
> fix for it. I have to compile postgres statically for the project I'm
> doing.

Why? Perhaps if you explained that in a bit more detail it'd be possible to 
find an alternative.

--
Craig Ringer

[GENERAL] pg_rman in Windows - is it possible?

[AI Rumman]

Could anyone please tell me whether I can use pg_rman in my Windows
environment? Or is there any similar type of application for windoes
Postgresql?

Re: [GENERAL] pg_rman in Windows - is it possible?

[Fujii Masao]

On Fri, Jul 1, 2011 at 6:18 PM, AI Rumman  wrote:
> Could anyone please tell me whether I can use pg_rman in my Windows
> environment?

http://code.google.com/p/pg-rman/wiki/Platforms

According to the above page, you cannot use it on Windows.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

-- 
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_rman in Windows - is it possible?

[Tomonari Katsumata]

Hi,

sorry, pg_rman can not use for windows environment.
This is an issue for a long time, but I can not revise it for some reasons.
(If you can make patch for revising them, welcome.)

(2011/07/01 18:18), AI Rumman wrote:

Could anyone please tell me whether I can use pg_rman in my Windows
environment? Or is there any similar type of application for windoes
Postgresql?


regards,
---
Tomonari Katsumata


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

[GENERAL] roles

[salah jubeh]

 
I have two databases, I need to insure that both databases has the same roles.  
tables, schemas, views must have the same permissions and privileges. you can 
say and Identical clones. I can synchronise the roles using these statments


SELECT DISTINCT 'CREATE USER '||usename||';' FROM pg_user;
SELECT 'GRANT '||g.usename||' TO '||u.usename||';' FROM pg_auth_members a JOIN 
pg_user u ON a.member = u.usesysid JOIN pg_user g ON a.roleid = g.usesysid;
SELECT 'ALTER ROLE '||usename||' WITH SUPERUSER;' FROM pg_user WHERE usesuper;

Re: [GENERAL] roles

[salah jubeh]

Hello,
 

I have two databases, I need to insure that both databases has the same roles.  
tables, schemas, views must have the same permissions and privileges. you can 
say and Identical clones. I can synchronize the roles using these statements


SELECT DISTINCT 'CREATE USER '||usename||';' FROM pg_user;
SELECT 'GRANT '||g.usename||' TO '||u.usename||';' FROM pg_auth_members a JOIN 
pg_user u ON a.member = u.usesysid JOIN pg_user g ON a.roleid = g.usesysid;
SELECT 'ALTER ROLE '||usename||' WITH SUPERUSER;' FROM pg_user WHERE usesuper;


  However, How can I synchronize the privileges that are assigned to the 
database entities i.e. schemas , tables, and views.


If that solution is not possible, how can I create an identical clone of my 
database including roles. when I use pg_restore normally the owner of the 
tables 
are changed and you need to recreate the roles in advance

Regards

Re: [GENERAL] roles

[Rob Richardson]

As I understand things, roles are not specific to databases.  In
PGAdmin, when I connect to a server, I see five collections of objects:
databases, tablespaces, jobs, group roles, and login roles.  Roles are
separate from databases.  So, for a given server, login and group roles
apply to all databases on that server.  

 

Roles can be exported using pg_dump's "global" option, which I think is
"-g".  Check the help for pg_dump.

 

Good luck!

 

RobR, whose advice may well be worth what you have paid for it.

 



From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of salah jubeh
Sent: Friday, July 01, 2011 8:07 AM
To: pgsql
Subject: Re: [GENERAL] roles

 

 

Hello,
 

I have two databases, I need to insure that both databases has the same
roles.  tables, schemas, views must have the same permissions and
privileges. you can say and Identical clones. I can synchronize the
roles using these statements

SELECT DISTINCT 'CREATE USER '||usename||';' FROM pg_user;

SELECT 'GRANT '||g.usename||' TO '||u.usename||';' FROM pg_auth_members
a JOIN pg_user u ON a.member = u.usesysid JOIN pg_user g ON a.roleid =
g.usesysid;

SELECT 'ALTER ROLE '||usename||' WITH SUPERUSER;' FROM pg_user WHERE
usesuper;





  However, How can I synchronize the privileges that are assigned to the
database entities i.e. schemas , tables, and views.


If that solution is not possible, how can I create an identical clone of
my database including roles. when I use pg_restore normally the owner of
the tables are changed and you need to recreate the roles in advance

Regards



 

 

Re: [GENERAL] roles

[Chris Travers]

On Fri, Jul 1, 2011 at 5:06 AM, salah jubeh  wrote:
>
> Hello,
>
> I have two databases, I need to insure that both databases has the same
> roles.  tables, schemas, views must have the same permissions and
> privileges. you can say and Identical clones. I can synchronize the roles
> using these statements

I guess it kinda depends on what you are trying to do.

If these are db's on the same db cluster, then no action is necessary
since roles are shared among databases.  If they are on different
clusters and servers, I think your approach will fail to synchronize
passwords if those are required.  I would probably do a pg_dumpall -s
and use grep to pullout the create/alter role statements along with
grants.

Best wishes,
Chris Travers

-- 
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_rman in Windows - is it possible?

[AI Rumman]

But is there any similar kind of application for Windows Postgresql Backup?

On Fri, Jul 1, 2011 at 3:39 PM, Tomonari Katsumata <
katsumata.tomon...@po.ntts.co.jp> wrote:

> Hi,
>
> sorry, pg_rman can not use for windows environment.
> This is an issue for a long time, but I can not revise it for some reasons.
> (If you can make patch for revising them, welcome.)
>
>
> (2011/07/01 18:18), AI Rumman wrote:
>
>> Could anyone please tell me whether I can use pg_rman in my Windows
>> environment? Or is there any similar type of application for windoes
>> Postgresql?
>>
>
> regards,
> ---
> Tomonari Katsumata
>
>
> --
> 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] question about query

[David Johnston]

-Original Message-
i have created the union to get all the records (giving payments negative
sign) but what i don't know how to do it is use a window function to create
the column with the running total, any short example of syntax please?

>>

You'll need to check the documentation for the exact syntax but the
following should work as desired.

SELECT description, amount, transaction_date, SUM(amount) OVER (ORDER BY
transaction_date, amount)
FROM accountsreceivable

You do NOT use a "GROUP BY" in this instance because the "OVER" clause
following the SUM function turns it into a Window function which aggregates
in the context of a virtual window.  Using ORDER BY causes the SUM()
function to behave like a Running Sum.

You have not specified your version and Window functions are only available
in newer releases (>=8.4 I believe).  If you cannot use this then you will
have to write a store procedure.

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] Anonymous record type and inner types.

[Adrian Klaver]

On Friday, July 01, 2011 1:37:44 am Ronan Dunklau wrote:
> Hello.
> 
> Sorry if this question has already been asked, but I couldn't find the
> answer anywhere.
> 
> When I execute a statement such as "Select (an_int, a_varchar) as a_record
> from test, the type returned for a_record is "record" (oid 2249).
> 
> Is the "inner type" information propagated somehow (that an_int is an
> integer, and a_varchar a varchar) ?

See below for complete information:
http://www.postgresql.org/docs/9.0/interactive/sql-expressions.html#SQL-SYNTAX-
ROW-CONSTRUCTORS

> 
> If so, is it easily accessible in client libraries ? I'm using psycopg2 for
> python, which itself uses libpq.
> 
> Thanks.
> 
> Regards,
> 
> --
> Ronan Dunklau

-- 
Adrian Klaver
adrian.kla...@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

[GENERAL] \i command in dblink

[AI Rumman]

I have a file as follows:

\set counter 10
\set name_test 'abc'

This  file is used to set some psql client variables.

My problem is that, how I can call this file from dblink connection.
That is, I need to use \i in dblink connection.
Any idea please.

-- 
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_upgrade does not translate tablespace location to new cluster

[Olivier LEVESQUE]

Hi,

I am currently testing pg_upgrade (in copy mode)  to migrate a 8.4.4
old cluster to a new 9.0.3 one.
It runs fine except when databases contain tablespaces


$pg_upgrade -d /pgqdata/pgserver01/data -D /pgqdata/pgserver02/data -b
/opt/pgsql/na/8.4.4/bin -B /opt/pgsql/na/9.0.3/bin -p 5432 -P 5433

Performing Consistency Checks
-
Checking old data directory (/pgqdata/pgserver01/data)  ok
Checking old bin directory (/opt/pgsql/na/8.4.4/bin)    ok
Checking new data directory (/pgqdata/pgserver02/data)  ok
Checking new bin directory (/opt/pgsql/na/9.0.3/bin)    ok
Checking for reg* system oid user data types    ok
Checking for /contrib/isn with bigint-passing mismatch  ok
Checking for large objects  ok
Creating catalog dump   ok
Checking for presence of required libraries ok

| If pg_upgrade fails after this point, you must
| re-initdb the new cluster before continuing.
| You will also need to remove the ".old" suffix
| from /pgqdata/pgserver01/data/global/pg_control.old.

Performing Migration

Adding ".old" suffix to old global/pg_control   ok
Analyzing all rows in the new cluster   ok
Freezing all rows on the new cluster    ok
Deleting new commit clogs   ok
Copying old commit clogs to new server  ok
Setting next transaction id for new cluster ok
Resetting WAL archives  ok
Setting frozenxid counters in new cluster   ok
Creating databases in the new cluster
psql:/opt/pgsql/bin/pg_upgrade_dump_globals.sql:38: ERROR:  directory
"/pgqdata/pgserver01/data/tbs_ptest/PG_9.0_201008051" already in use
as a tablespace


The failing command in pg_upgrade_dump_globals.sql is:

CREATE TABLESPACE tbs_ptest OWNER ptestowner LOCATION
'/pgqdata/pgserver01/data/tbs_ptest';

===> I would prefer that pg_upgrade translate the location to one in
the new cluster data directory (PGDATA), i.e.
/pgqdata/pgserver02/data/tbs_ptest


Does anyone know a possible workaround for this problem ?

Thank you.
--
Olivier LEVESQUE

-- 
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] Anonymous record type and inner types.

[Daniele Varrazzo]

On Fri, Jul 1, 2011 at 9:37 AM, Ronan Dunklau  wrote:

> When I execute a statement such as "Select (an_int, a_varchar) as a_record
> from test, the type returned for a_record is "record" (oid 2249).
>
> Is the "inner type" information propagated somehow (that an_int is an integer,
> and a_varchar a varchar) ?
>
> If so, is it easily accessible in client libraries ? I'm using psycopg2 for
> python, which itself uses libpq.

I don't think the information about the components of the composite
type are automatically propagated to the client nor made accessible by
the libpq.

You may create an user-defined type representing the record you select
and cast the result to it: for example if you "create type thing as
(the_int integer, the_text varchar);" and then "select (an_int,
a_varchar)::thing from test", postgres will pass the "thing" oid to
the client, instead of the one of the generic "record". psycopg can be
programmed to return a tuple/namedtuple allowing you to get the
components as python values: see

for further details.

-- Daniele

-- 
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_upgrade does not translate tablespace location to new cluster

[Guillaume Lelarge]

On Fri, 2011-07-01 at 16:24 +0200, Olivier LEVESQUE wrote:
> Hi,
> 
> I am currently testing pg_upgrade (in copy mode)  to migrate a 8.4.4
> old cluster to a new 9.0.3 one.
> It runs fine except when databases contain tablespaces
> 
> 
> $pg_upgrade -d /pgqdata/pgserver01/data -D /pgqdata/pgserver02/data -b
> /opt/pgsql/na/8.4.4/bin -B /opt/pgsql/na/9.0.3/bin -p 5432 -P 5433
> 
> Performing Consistency Checks
> -
> Checking old data directory (/pgqdata/pgserver01/data)  ok
> Checking old bin directory (/opt/pgsql/na/8.4.4/bin)ok
> Checking new data directory (/pgqdata/pgserver02/data)  ok
> Checking new bin directory (/opt/pgsql/na/9.0.3/bin)ok
> Checking for reg* system oid user data typesok
> Checking for /contrib/isn with bigint-passing mismatch  ok
> Checking for large objects  ok
> Creating catalog dump   ok
> Checking for presence of required libraries ok
> 
> | If pg_upgrade fails after this point, you must
> | re-initdb the new cluster before continuing.
> | You will also need to remove the ".old" suffix
> | from /pgqdata/pgserver01/data/global/pg_control.old.
> 
> Performing Migration
> 
> Adding ".old" suffix to old global/pg_control   ok
> Analyzing all rows in the new cluster   ok
> Freezing all rows on the new clusterok
> Deleting new commit clogs   ok
> Copying old commit clogs to new server  ok
> Setting next transaction id for new cluster ok
> Resetting WAL archives  ok
> Setting frozenxid counters in new cluster   ok
> Creating databases in the new cluster
> psql:/opt/pgsql/bin/pg_upgrade_dump_globals.sql:38: ERROR:  directory
> "/pgqdata/pgserver01/data/tbs_ptest/PG_9.0_201008051" already in use
> as a tablespace
> 

That would mean that you have a 9.0 tablespace in
the /pgqdata/pgserver01/data/tbs_ptest directory. Is it true? and IIUC
your directory layout, it shouldn't.

> The failing command in pg_upgrade_dump_globals.sql is:
> 
> CREATE TABLESPACE tbs_ptest OWNER ptestowner LOCATION
> '/pgqdata/pgserver01/data/tbs_ptest';
> 
> ===> I would prefer that pg_upgrade translate the location to one in
> the new cluster data directory (PGDATA), i.e.
> /pgqdata/pgserver02/data/tbs_ptest
> 

It would work for you if all the tablespaces in pgserver01 are in the
same base directory, and all the tablespaces in pgserver02 should be in
the same base directory. This is a very limited use case.

> Does anyone know a possible workaround for this problem ?
> 

The only way is to change the code. It shouldn't be too hard, but is
potentially dangerous.

Otherwise, nope.


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.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] statically compiling postgres and problem with initdb

[Craig Ringer]

On 1/07/2011 5:11 PM, mona attariyan wrote:

> CFLAGS="-O0 -I/[PATH]/glibc-2.5.1-custom/prefix/include -static

Yeah, it's not as simple as that, because Pg expects to be dynamically 
linked and to be able to dlopen() libraries and dlsym() resolve function 
pointers from them at runtime.


> creating conversions ... FATAL: could not load library
> "/usr/local/pgsql/lib/ascii_and_mic.so":
> /usr/local/pgsql/lib/ascii_and_mic.so: undefined symbol: pg_ascii2mic

... like that. You'll have to modify Pg to statically link such required 
libraries in, and add a new lookup method that can return function 
pointers to the internal statically linked copies of the functions when 
they're required.



I'm using Postgres to evaluate a research tool and the tool doesn't work
with dynamic libraries. That's why I need to compile it statically.


OK, so you're not trying to embed Pg into an application or make it run 
from a single executable. You just need "postgres", initdb, etc to each 
be a statically linked executable. That's good; some people who ask that 
sort of thing are angling for embedding it in an app, and it really 
won't work.


For your purposes, you'll have to modify PostgreSQL to support being 
built statically. It *expects* to be able to dlopen() libraries at 
runtime, and those libraries often expect to be able to resolve symbols 
from the postgres executable they're being linked into. This won't work 
when it's a static binary. You'll have to figure out which libraries 
each Pg executable needs during its lifetime, statically link them in as 
well, and modify postgresql to know which libraries are linked in at 
startup so it doesn't try to dlopen() them.


You can probably get Pg to use static copies of libraries it normally 
dlopen()s by building mappings of library names to structs full of 
function pointers that you return instead of the usual dlysm()'d 
function pointer from a dlopen()'d library. If you do it that way, the 
rest of Pg might not even need to know it's not truly dynamically 
loading things.


--
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] statically compiling postgres and problem with initdb

[Tom Lane]

Craig Ringer  writes:
> On 1/07/2011 5:11 PM, mona attariyan wrote:
>> I'm using Postgres to evaluate a research tool and the tool doesn't work
>> with dynamic libraries. That's why I need to compile it statically.

> For your purposes, you'll have to modify PostgreSQL to support being 
> built statically.

It might be easier to rip out the functionality that expects loadable
libraries to work.  I think you could probably get through initdb if you
just disabled creation of encoding-conversion functions and text search
dictionaries (try #ifdef'ing out the relevant sections of initdb.c).

Of course, you'll end up with a pretty crippled version of PG --- no
encoding conversions, no text search, no procedural languages --- but
maybe that's enough for what you want to do.  If it's not, then as Craig
says, you're looking at some pretty major work to bind those pieces into
the executable statically.

regards, tom lane

-- 
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_upgrade does not translate tablespace location to new cluster

[Olivier LEVESQUE]

Guillaume,

Thank you for your answer,


>> Creating databases in the new cluster
>> psql:/opt/pgsql/bin/pg_upgrade_dump_globals.sql:38: ERROR:  directory
>> "/pgqdata/pgserver01/data/tbs_ptest/PG_9.0_201008051" already in use
>> as a tablespace
>>
>
> That would mean that you have a 9.0 tablespace in
> the /pgqdata/pgserver01/data/tbs_ptest directory. Is it true? and IIUC
> your directory layout, it shouldn't.
>

Yes, you're right.  This directory was here from my last succesfull pg_upgrade.

But the problem is that now, data of the new cluster is scattered
across two paths which is not what I expected.


> The only way is to change the code. It shouldn't be too hard, but is
> potentially dangerous.

Why dangerous ? It could be an option.


Regards,
--
Olivier LEVESQUE

-- 
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_upgrade does not translate tablespace location to new cluster

[Guillaume Lelarge]

On Fri, 2011-07-01 at 18:30 +0200, Olivier LEVESQUE wrote:
> Guillaume,
> 
> Thank you for your answer,
> 
> 
> >> Creating databases in the new cluster
> >> psql:/opt/pgsql/bin/pg_upgrade_dump_globals.sql:38: ERROR:  directory
> >> "/pgqdata/pgserver01/data/tbs_ptest/PG_9.0_201008051" already in use
> >> as a tablespace
> >>
> >
> > That would mean that you have a 9.0 tablespace in
> > the /pgqdata/pgserver01/data/tbs_ptest directory. Is it true? and IIUC
> > your directory layout, it shouldn't.
> >
> 
> Yes, you're right.  This directory was here from my last succesfull 
> pg_upgrade.
> 
> But the problem is that now, data of the new cluster is scattered
> across two paths which is not what I expected.
> 
> 
> > The only way is to change the code. It shouldn't be too hard, but is
> > potentially dangerous.
> 
> Why dangerous ? It could be an option.
> 

I didn't mean the option is dangerous. My point is: I don't think it
would be interesting to add this change to the mainstream pg_upgrade
because the usecase is very limited (others will correct me if I'm
wrong), so the other option is to code it yourself. And this might be
dangerous (because of very limited tests, and so forth).


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


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

[GENERAL] Adding Foreign Key Constraint To Existing Table

[Rich Shepard]

  I've examined the 9.0 manual page on alter table without seeing how to add
a foreign key constraint to a column.

  I needed to make changes on a couple of existing tables which could be
accomplished only by dropping the foreign key constraint. That, and changing
the table structure, column names, and column types were successful. But, I
am not seeing the proper syntax to add a foreign key constraint back to the
two affected tables.

  Pointer please.

Rich


--
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] Adding Foreign Key Constraint To Existing Table

[Adrian Klaver]

On Friday, July 01, 2011 2:00:38 pm Rich Shepard wrote:
>I've examined the 9.0 manual page on alter table without seeing how to
> add a foreign key constraint to a column.
> 
>I needed to make changes on a couple of existing tables which could be
> accomplished only by dropping the foreign key constraint. That, and
> changing the table structure, column names, and column types were
> successful. But, I am not seeing the proper syntax to add a foreign key
> constraint back to the two affected tables.
> 
>Pointer please.

http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html

Examples at bottom of page:

"To add a foreign key constraint to a table:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES 
addresses (address) MATCH FULL;
"
> 
> Rich

-- 
Adrian Klaver
adrian.kla...@gmail.com

Re: [GENERAL] Adding Foreign Key Constraint To Existing Table

[Joshua D. Drake]

On 07/01/2011 02:00 PM, Rich Shepard wrote:

I've examined the 9.0 manual page on alter table without seeing how to add
a foreign key constraint to a column.

I needed to make changes on a couple of existing tables which could be
accomplished only by dropping the foreign key constraint. That, and
changing
the table structure, column names, and column types were successful. But, I
am not seeing the proper syntax to add a foreign key constraint back to the
two affected tables.

Pointer please.


alter table bar add foreign key (id) references foo(id);

JD



Rich





--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

--
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] Adding Foreign Key Constraint To Existing Table

[Thom Brown]

On 1 July 2011 22:00, Rich Shepard  wrote:
>  I've examined the 9.0 manual page on alter table without seeing how to add
> a foreign key constraint to a column.
>
>  I needed to make changes on a couple of existing tables which could be
> accomplished only by dropping the foreign key constraint. That, and changing
> the table structure, column names, and column types were successful. But, I
> am not seeing the proper syntax to add a foreign key constraint back to the
> two affected tables.

ALTER TABLE table_a ADD CONSTRAINT fk_name
FOREIGN KEY (column_of_table_a) REFERENCES table_b (column_of_table_b);

If in future you want foreign key checks to be deferred until the
transaction ends, you can add the DEFERRED keyword to the end.  This
will allow you to violate the foreign key temporarily, as long as you
resolve it before the end of the transaction.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] Adding Foreign Key Constraint To Existing Table

[Rich Shepard]

On Fri, 1 Jul 2011, Joshua D. Drake wrote:


alter table bar add foreign key (id) references foo(id);


  Thanks, Josh. I was close, but not exact.

Rich

--
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] Adding Foreign Key Constraint To Existing Table

[Thom Brown]

On 1 July 2011 22:00, Rich Shepard  wrote:
>  I've examined the 9.0 manual page on alter table without seeing how to add
> a foreign key constraint to a column.
>
>  I needed to make changes on a couple of existing tables which could be
> accomplished only by dropping the foreign key constraint. That, and changing
> the table structure, column names, and column types were successful. But, I
> am not seeing the proper syntax to add a foreign key constraint back to the
> two affected tables.

By the way, rather than dropping the foreign key then recreating it,
you could always do this:

ALTER TABLE tablename DISABLE TRIGGER ALL;

Then it would ignore the foreign key trigger and you could put in
mischievous values... but remember to enable it again (replace DISABLE
with ENABLE).  You'll have to be a superuser to do it though.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] Adding Foreign Key Constraint To Existing Table

[Rich Shepard]

On Fri, 1 Jul 2011, Thom Brown wrote:


By the way, rather than dropping the foreign key then recreating it, you
could always do this:

ALTER TABLE tablename DISABLE TRIGGER ALL;

Then it would ignore the foreign key trigger and you could put in
mischievous values... but remember to enable it again (replace DISABLE
with ENABLE).  You'll have to be a superuser to do it though.


Thom,

  Valuable information, thanks. I try to get the table structure correct
before loading it into a database. In this case I was copying structure
(with minor mods) from an existing environmental database and it took me a
while to notice the original authors used synthetic keys when they are not
needed. So, I got rid of those keys.

Rich

--
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] Adding Foreign Key Constraint To Existing Table

[David Johnston]

A foreign key is a kind of constraint.  Section 5.5.3 has example syntax to add 
constraints, including a foreign key constraint.

In alter table the part "ADD table_constraint" is what you want to follow (it 
leads you to the create table page for the formal syntax to use).

David J.

On Jul 1, 2011, at 17:00, Rich Shepard  wrote:

>  I've examined the 9.0 manual page on alter table without seeing how to add
> a foreign key constraint to a column.
> 
>  I needed to make changes on a couple of existing tables which could be
> accomplished only by dropping the foreign key constraint. That, and changing
> the table structure, column names, and column types were successful. But, I
> am not seeing the proper syntax to add a foreign key constraint back to the
> two affected tables.
> 
>  Pointer please.
> 
> Rich
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

-- 
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] statically compiling postgres and problem with initdb

[Craig Ringer]

On 1/07/2011 11:48 PM, Tom Lane wrote:


It might be easier to rip out the functionality that expects loadable
libraries to work.  I think you could probably get through initdb if you
just disabled creation of encoding-conversion functions and text search
dictionaries (try #ifdef'ing out the relevant sections of initdb.c).


Good point. If all that's needed is basic functionality...


Of course, you'll end up with a pretty crippled version of PG --- no
encoding conversions, no text search, no procedural languages --- but
maybe that's enough for what you want to do.  If it's not, then as Craig
says, you're looking at some pretty major work to bind those pieces into
the executable statically.


I had to do something quite similar for Scribus years ago, and it wasn't 
anywhere near as hard as I'd feared. It did have two  really annoying 
bits, though. One was having to prefix each shared library's public 
symbols with the name of the shared library to avoid conflicts and allow 
me to differentiate different implementations of the same public 
interfaces. The second was modifying the build system to link each 
library to the main executable.


This is from increasingly vague memory, but:

First I created a function pointer list entry struct type that maps 
function names to function pointers.


I then modified the loader code so it prefixed the function names it was 
expecting with the library name. Instead of "funcname" it'd try to 
resolve "libname_funcname".


I went through EVERY SINGLE LIBRARY and prefixed the library name to the 
names of every non-static function. It was ugly, but the alternative 
would've been a horrid token-pasting macro hack that would've still 
required changing each function declaration.


The libraries already had headers. If they hadn't, I would've had to 
write a header for each library that declared prototypes for its functions.


I added a new header file to the main build that included all the 
library headers and declared a global array of function pointer list 
entries. In the associated .c (well, .cxx in Scribus) I defined the 
array, populating it with the library-name-prefixed function names and 
pointers to each function.


I modified each site where Scribus used dlopen() and dlsym() so it 
called them via a wrapper. The wrapper for dynamic linking was just a 
trivial header of inline wrappers around dlopen() and dlsym(). The 
static linking replacement for dlopen() just returned the input library 
name char* as void*, and the dlsym() replacement cast the void* back to 
char*, joined the library name and function name, looked the result up 
in the function pointer list, and returned the resulting function pointer.


Finally, I had to modify the build process so it produced static 
libraries instead of shared libraries for each add-on, and modify the 
final application linkage so it linked each library. This was made a lot 
easier by the fact that Scribus used CMake instead of autotools; I don't 
know what it'd be like to try to do this with autohell, but I suspect 
"ugly" would be a start.


--
Craig Ringe

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

[GENERAL] dblink and insert?

[Andrew Shved]

I want to ghave a driger from host1, database1, table1 to host2,
database2, table2.  To insert the same row into table2 only if the row
matches certain conditions.  I am not clear how to write dblink
function to do that.

Can someone ofer a piece of code to put inside of my trigger function
on  host1, database1, table1.  I tried different variations and all of
them fail.  Or db link only reliably works on selects? by the way I am
using postgresql 8.3.

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