Re: [GENERAL] problems after restoring from a pg_basebackup

[Guillaume Lelarge]

On Fri, 2012-04-27 at 16:25 -0700, Lonni J Friedman wrote:
> Greetings,
> I'm running postgresql-9.1.3 on a Linux-x86_64 (Fedora16, if it
> matters) system.  I noticed the existence of pg_basebackup starting in
> 9.1, and figured I'd try it out and see if it would simplify our
> backup & management processes.
> 
> $ pg_basebackup -P -v -D /tmp/backup -x -Ft -z -U postgres
> xlog start point: C6/6420
> 135733616/135733616 kB (100%), 1/1 tablespace
> xlog end point: C6/64A0
> pg_basebackup: base backup completed
> 
> So after running through this, I tried to use (restore) the backup
> that was generated.  While everything appears to be working ok from a
> functional perspective, in the server log I saw the following:
> ##
> LOG:  creating missing WAL directory "pg_xlog/archive_status"
> LOG:  database system was not properly shut down; automatic recovery in 
> progress
> LOG:  redo starts at C6/6678
> LOG:  could not open file "pg_xlog/000100C60067" (log file
> 198, segment 103): No such file or directory
> LOG:  redo done at C6/66A0
> FATAL:  the database system is starting up
> LOG:  autovacuum launcher started
> LOG:  database system is ready to accept connections
> #
> 
> Just to be clear, here's what I did after pg_basebackup had completed
> successfully:
> 0) shutdown postgresql gracefully, and verified that it was fully shutdown
> 1) moved $PGDATA to $PGDATA.old
> 2) created $PGDATA as postgres user
> 3) extracted the basebackup tarball as postgres user
> cd $PGDATA && tar xzvpf /tmp/backup/base.tar.gz
> 4) started postgresql up
> 
> I would have expected that I wouldn't have gotten the 'not properly
> shutdown' warning, or the 'could not open file' warning by following
> this process.  Am I doing something wrong?
> 

No, I think you did right. The first error message is right: you didn't
stop PostgreSQL. You were right not to stop it, but PostgreSQL is right
to say that it wasn't stopped, and that it has to replay some logs (if
there was some activity during the backup). The second error message
says that this specific wal file is not available and thus it has
nothing more to replay. Seems good to me.


-- 
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] postgresql standby using pg_archivecleanup don't work

[raghu ram]

On Fri, Apr 27, 2012 at 11:44 AM, leo xu  wrote:

> i have one parimary ,two standby. one standby using stream replication
> connect to primary.another standby using basebackup,then recovery database
> --cp archivelog from primary using crontab.i want to delete old archivelog
> from standby.but configure pg_archivecleanup don't work,meantime no error
> in
> postgresql log.postgresql version is 9.1.2.
>   the following is my recovery.conf :
>   This is my recovery.conf:
>
> standby_mode = 'on'
> restore_command = 'cp /attend_pg_backup/u02/pg9.1/archivelog/%f %p'
> archive_cleanup_command ='pg_archivecleanup
> /attend_pg_backup/u02/pg9.1/archivelog %r'
>
>
>
>
Replace archive_cleanup_command in recovery.conf file as follows:

archive_cleanup_command
='pg_archivecleanup /attend_pg_backup/u02/pg9.1/archivelog %r'

   [to]

archive_cleanup_command = '/opt/PostgreSQL/9.1/bin/pg_archivecleanup
-d /attend_pg_backup/u02/pg9.1/archivelog  %r 2>>/tmp/cleanup.log'

and then restart the Standby PostgreSQL cluster.

--

Thanks & Regards,

Raghu Ram

EnterpriseDB: http://www.enterprisedb.com

[GENERAL] problems after restoring from a pg_basebackup

[Lonni J Friedman]

Greetings,
I'm running postgresql-9.1.3 on a Linux-x86_64 (Fedora16, if it
matters) system.  I noticed the existence of pg_basebackup starting in
9.1, and figured I'd try it out and see if it would simplify our
backup & management processes.

$ pg_basebackup -P -v -D /tmp/backup -x -Ft -z -U postgres
xlog start point: C6/6420
135733616/135733616 kB (100%), 1/1 tablespace
xlog end point: C6/64A0
pg_basebackup: base backup completed

So after running through this, I tried to use (restore) the backup
that was generated.  While everything appears to be working ok from a
functional perspective, in the server log I saw the following:
##
LOG:  creating missing WAL directory "pg_xlog/archive_status"
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at C6/6678
LOG:  could not open file "pg_xlog/000100C60067" (log file
198, segment 103): No such file or directory
LOG:  redo done at C6/66A0
FATAL:  the database system is starting up
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
#

Just to be clear, here's what I did after pg_basebackup had completed
successfully:
0) shutdown postgresql gracefully, and verified that it was fully shutdown
1) moved $PGDATA to $PGDATA.old
2) created $PGDATA as postgres user
3) extracted the basebackup tarball as postgres user
cd $PGDATA && tar xzvpf /tmp/backup/base.tar.gz
4) started postgresql up

I would have expected that I wouldn't have gotten the 'not properly
shutdown' warning, or the 'could not open file' warning by following
this process.  Am I doing something wrong?

thanks

-- 
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 get the all the activities running during specific time range.Thanks!Plese help!

[Josh Kupershmidt]

On Fri, Apr 27, 2012 at 7:32 AM, leaf_yxj  wrote:
> I was asked to run a report to my boss all the activities running during a
> specific time range. I want to create a script to run it. I know I can query
> from  pg_stat_activity for my previous postgres version. But I can't find
> this system table in 8.2.15. Please help. Thanks. Regards. Grace.

According to the docs, the pg_stat_activity view should exist in 8.2:
  http://www.postgresql.org/docs/8.2/static/monitoring-stats.html

But I think it's the wrong tool for finding "all the activities
running during a specific time range". I would instead recommend
setting log_min_duration_statement to 0, at least for the time period
you are interested in, so that you can be sure you are logging all
queries:
  http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html

Josh

-- 
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 add "on delete cascade" constraints

[Alexander Farber]

Thank you - this has worked perfectly

On Fri, Apr 27, 2012 at 10:18 PM, Richard Broersma
 wrote:
> You could, but you don't need to since you can do all of this is one 
> statement:
>
> ALTER TABLE public.pref_scores
> DROP CONSTRAINT pref_scores_gid_fkey,
> ADD CONSTRAINT pref_scores_gid_fkey
>    FOREIGN KEY (gid)
>    REFERENCES pref_games(gid)
>    ON DELETE CASCADE;

-- 
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 add "on delete cascade" constraints

[Richard Broersma]

On Fri, Apr 27, 2012 at 12:40 PM, Alexander Farber
 wrote:
> So it's not a problem to drop and recreate the FOREIGN KEYs?
>
> And can I use START TRANSACTION while doing it?

You could, but you don't need to since you can do all of this is one statement:

ALTER TABLE public.pref_scores
DROP CONSTRAINT pref_scores_gid_fkey,
ADD CONSTRAINT pref_scores_gid_fkey
FOREIGN KEY (gid)
REFERENCES pref_games(gid)
ON DELETE CASCADE;


-- 
Regards,
Richard Broersma Jr.

-- 
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 add "on delete cascade" constraints

[Raymond O'Donnell]

On 27/04/2012 20:40, Alexander Farber wrote:
> So it's not a problem to drop and recreate the FOREIGN KEYs?

No, unless you're doing it on a production system, and someone inserts a
value that'll doesn't agree with the foreign key while it's gone.

Adding the constraint back in may take a while (depending on how big
your table is) as contents of the columns involved need to be checked to
ensure they contain valid values.

Also, I think maybe an exclusive lock is required, which will block out
other users - not sure about this - others on this list will know.

> And can I use START TRANSACTION while doing it?

Yes, absolutely - transactional DDL is one of PG's great strengths.

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] how to execute a stored function that returns a boolean?

[Merlin Moncure]

On Fri, Apr 27, 2012 at 2:43 PM, J.V.  wrote:
> I have created a stored function and wish to execute in pgadmin but keep
> getting an error.
>
> create or replace function myfunc() returns boolean
> as $$
> declare
> begin
> ...
> end;
> $$ language plpgsql;
>
> compiles and works just find, however when I run (in pgadmin III)
>
> select myfunc();
>
> I get errors telling me I need to use 'perform' instead, but when I run:


that error is being thrown from indie the function.  somewhere inside
the function body you are using select where you should be using
perform.

> perform myfunc(); it says : syntax error at or near "perform"
> LINE 1: perform myfunc();

perform only makes sense inside functions. it has not meaning except inside sql.

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] how to execute a stored function that returns a boolean?

[Raymond O'Donnell]

On 27/04/2012 20:43, J.V. wrote:
> I have created a stored function and wish to execute in pgadmin but keep
> getting an error.
> 
> create or replace function myfunc() returns boolean
> as $$
> declare
> begin
> ...
> end;
> $$ language plpgsql;
> 
> compiles and works just find, however when I run (in pgadmin III)
> 
> select myfunc();
> 
> I get errors telling me I need to use 'perform' instead, but when I run:

Can you show us the rest of your function? In pl/pgsql, you use PERFORM
instead of SELECT when you don't need the result of a query; you
probably have a SELECT in there somewhere.

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

[GENERAL] how to execute a stored function that returns a boolean?

[J.V.]

I have created a stored function and wish to execute in pgadmin but keep 
getting an error.


create or replace function myfunc() returns boolean
as $$
declare
begin
...
end;
$$ language plpgsql;

compiles and works just find, however when I run (in pgadmin III)

select myfunc();

I get errors telling me I need to use 'perform' instead, but when I run:

perform myfunc(); it says : syntax error at or near "perform"
LINE 1: perform myfunc();



--
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 add "on delete cascade" constraints

[Alexander Farber]

So it's not a problem to drop and recreate the FOREIGN KEYs?

And can I use START TRANSACTION while doing it?

On Fri, Apr 27, 2012 at 9:30 PM, Raymond O'Donnell  wrote:
> On 27/04/2012 19:59, Alexander Farber wrote:
>> in 8.4.9 is it please possible to add "on delete cascades" to
>> the both foreign keys in the following table w/o dropping the table?
>
> I think you will have to drop the foreign key and re-create it, but
> certainly not the table.

-- 
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 add "on delete cascade" constraints

[Raymond O'Donnell]

On 27/04/2012 19:59, Alexander Farber wrote:
> Hello,
> 
> in 8.4.9 is it please possible to add "on delete cascades" to
> the both foreign keys in the following table w/o dropping the table?

I think you will have to drop the foreign key and re-create it, but
certainly not the table.

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

[GENERAL] How to add "on delete cascade" constraints

[Alexander Farber]

Hello,

in 8.4.9 is it please possible to add "on delete cascades" to
the both foreign keys in the following table w/o dropping the table?

# \d pref_scores
 Table "public.pref_scores"
 Column  | Type  | Modifiers
-+---+---
 id  | character varying(32) |
 gid | integer   |
 money   | integer   | not null
 quit| boolean   |
 last_ip | inet  |
Foreign-key constraints:
"pref_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES pref_games(gid)
"pref_scores_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id)

There referenced tables are below:

# \d pref_games
 Table "public.pref_games"
  Column  |Type |Modifiers
--+-+--
 gid  | integer | not null default
nextval('pref_games_gid_seq'::regclass)
 rounds   | integer | not null
 finished | timestamp without time zone | default now()
Indexes:
"pref_games_pkey" PRIMARY KEY, btree (gid)
Referenced by:
TABLE "pref_scores" CONSTRAINT "pref_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES pref_games(gid)

 \d pref_users
Table "public.pref_users"
   Column   |Type |   Modifiers
+-+---
 id | character varying(32)   | not null
 first_name | character varying(64)   |
 last_name  | character varying(64)   |
 female | boolean |
 avatar | character varying(128)  |
 city   | character varying(64)   |
 login  | timestamp without time zone | default now()
 last_ip| inet|
 logout | timestamp without time zone |
 vip| timestamp without time zone |
 mail   | character varying(254)  |
Indexes:
"pref_users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "pref_cards" CONSTRAINT "pref_cards_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_catch" CONSTRAINT "pref_catch_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_chat" CONSTRAINT "pref_chat_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_game" CONSTRAINT "pref_game_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_hand" CONSTRAINT "pref_hand_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_luck" CONSTRAINT "pref_luck_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_match" CONSTRAINT "pref_match_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_misere" CONSTRAINT "pref_misere_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_money" CONSTRAINT "pref_money_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_pass" CONSTRAINT "pref_pass_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_payment" CONSTRAINT "pref_payment_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_rep" CONSTRAINT "pref_rep_author_fkey" FOREIGN KEY
(author) REFERENCES pref_users(id)
TABLE "pref_rep" CONSTRAINT "pref_rep_id_fkey" FOREIGN KEY (id)
REFERENCES pref_users(id)
TABLE "pref_scores" CONSTRAINT "pref_scores_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)
TABLE "pref_status" CONSTRAINT "pref_status_id_fkey" FOREIGN KEY
(id) REFERENCES pref_users(id)

Regards
Alex

-- 
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] Re: how to set up automatically startup database when the server boot or reboot.

[Welty, Richard]

in the RHEL and related linux systems (Fedora, CentOS, Amazon EC2 Linux), use 
this
command:

chkconfig postgresql on

to set up postgresql to start at boot. it needs to be executed as root.

richard

-Original Message-
From: pgsql-general-ow...@postgresql.org on behalf of leaf_yxj
Sent: Fri 4/27/2012 1:22 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Re: how to set up automatically startup database when the 
server boot or reboot.
 
My os is redhat linux 5.5. And My database is greenplum 4.2.1( postgresql
8.2.15).  I will take a look about the init.d directory.

Thanks. Guys. Any opinion is welcome. Please help.


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670905.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Re: how to set up automatically startup database when the server boot or reboot.

[John R Pierce]

On 04/27/12 10:22 AM, leaf_yxj wrote:

My os is redhat linux 5.5. And My database is greenplum 4.2.1( postgresql
8.2.15).  I will take a look about the init.d directory.


greenplum is a highly modified and custom version of postgres, you 
should consult with them as to how they have it setup.



the standard postgresql distributions for RHEL/CentOS come with the 
SysVinit scripts, you merely need to..


# chkconfig postgresql-9.1 on
# service postgresql-9.1 start

to configure it to autostart with the OS, and to start it right now.


--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
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] empty role names in pg_dumpall output

[Filip Rembiałkowski]

On Fri, Apr 27, 2012 at 10:23 AM, Tom Lane  wrote:

> Ah-ha.  How about the member and grantor OIDs in those rows --- do
> they correspond to still-existing roles?  (I believe "10" would be
> the bootstrap superuser, so that should certainly still exist, but
> those other numbers are for user-made roles.)

The grantors still exists, the members - some yes, some not.

>
>> Could it be (theoretically) caused by human-made insertions into
>> pg_auth_members?
>
> Well, perhaps ... are you in the habit of hacking that catalog directly?

No, I dont have this bad habit, but the database is several years old,
and I can't rule it out.


I wonder if this case can be taken as catalog corruption, and maybe
postgres should guard users against it.

When you delete a role, its memberships are deleted.

Maybe there should be a NOT NULL && FK constraint on member and grantor?

I'm not that much into pg_catalog, maybe there is a good reason for
NOT having it.



Anyway, on my side the problem is closed, we just delete them and the
pg_dumpall output is fixed.


Thank you

-- 
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] Re: how to set up automatically startup database when the server boot or reboot.

[Michael Nolan]

Your options range from doing something simple to something complex.

A simple option on a Linux server would be placing a command like this in
/etc/rc/rc.local:

su - postgres -C "/usr/local/pgsql/bin/pg_ctl -D ;/usr/local/pgsql/data -l
/usr/local/pgsql/logfile start"

However, that might not be the optimal choice for every situation, because
it doesn't take into account WHY the system rebooted.  A system that
rebooted because of a power/UPS issue might need to be treated differently
than one that rebooted because of a hardware failure or kernel panic.  (And
just because postgres can restart the database, that doesn't always mean it
should.  Even a well-written startup script might not know enough to make
that decision for you.)

This might be good material for a tutorial on the wiki site, with some
system-specific sections.
--
Mike Nolan

Re: [GENERAL] Re: how to set up automatically startup database when the server boot or reboot.

[h...@101-factory.eu]

are there any usefull startup script when eiunning in master slave setup with 
pg pool?

Henk 


On 27 apr. 2012, at 19:22, leaf_yxj  wrote:

> My os is redhat linux 5.5. And My database is greenplum 4.2.1( postgresql
> 8.2.15).  I will take a look about the init.d directory.
> 
> Thanks. Guys. Any opinion is welcome. Please help.
> 
> 
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670905.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> 
> -- 
> 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] empty role names in pg_dumpall output

[Tom Lane]

=?UTF-8?Q?Filip_Rembia=C5=82kowski?=  writes:
> On Wed, Apr 25, 2012 at 8:56 PM, Tom Lane  wrote:
>> Hmm.  A look at the code in pg_dumpall suggests that the problem is
>> unmatched entries in pg_auth_members, ie this query:
>> 
>> SELECT ur.rolname AS roleid
>> FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid
>> 
>> is returning some null results.

> Yes that is the case:

Ah-ha.  How about the member and grantor OIDs in those rows --- do
they correspond to still-existing roles?  (I believe "10" would be
the bootstrap superuser, so that should certainly still exist, but
those other numbers are for user-made roles.)

> Could it be (theoretically) caused by human-made insertions into
> pg_auth_members?

Well, perhaps ... are you in the habit of hacking that catalog directly?
Aside from the possibility of inserting a just-plain-wrong OID, there's
the possibility of inserting a valid row but forgetting to add a
pg_shdepend entry that would protect the row against the underlying
role being dropped.

> Maybe you remember some bug which could have caused this in the past?

Doesn't ring a bell offhand.

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

[GENERAL] Re: how to set up automatically startup database when the server boot or reboot.

[leaf_yxj]

My os is redhat linux 5.5. And My database is greenplum 4.2.1( postgresql
8.2.15).  I will take a look about the init.d directory.

Thanks. Guys. Any opinion is welcome. Please help.


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670905.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to create a sequence in a stored proc?

[Steve Atkins]

On Apr 27, 2012, at 9:35 AM, J.V. wrote:

> Right, I understand that fully, and have used SQL inside a stored proc 
> before, but in this case as I mentioned, I need to first do a select from a 
> table to get a max value, store that in a variable and then use that variable 
> in a create sequence sql statement.

Another approach would be to create the sequence then set the value - you can 
do that without needing anything more than SQL:

  create sequence foo
  select setval('foo', (select max(bar) from baz)


> so I need to construct a string that contains the create sequence statement 
> and execute that string, at least this is the way it is done in Oracle.
> 
> I do not know how to use a variable in a create sequence statement in 
> PostgreSQL.

It depends on the language you're using. For plpgsql it's covered in more 
detail in the docs, but you could do something like:

  create function make_sequence() returns void as $$
declare
  newvalue integer;
begin
  select max(bar)+1 from baz into newvalue;
  execute 'create sequence foo start ' || newvalue;
end;
  $$ language plpgsql;


Cheers,
  Steve

> 
> J.V.
> 
> On 4/27/2012 9:51 AM, Merlin Moncure wrote:
>> On Fri, Apr 27, 2012 at 10:37 AM, J.V.  wrote:
>>> I need to create a sequence in a stored procedure.
>>> 
>>> First I need to select a value from a table and set the sequence start value
>>> to that value.
>>> 
>>> We have a table that does not have a sequence on it, so I want to select the
>>> max value, increment by one
>>> and then start the sequence there.
>>> 
>>> We have to do this on three databases, I have figured out how to do this in
>>> Oracle (build a string and the EXECUTE IMMEDIATE, but have not
>>> figured out how to do this with PostgreSQL.
>> just about any sql is allowed from within a function, including CREATE
>> SEQUENCE.  functions can even create functions and execute them.
>> 
>> merlin
>> 
> 
> -- 
> 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] how to create a sequence in a stored proc?

[Raymond O'Donnell]

On 27/04/2012 17:35, J.V. wrote:
> Right, I understand that fully, and have used SQL inside a stored proc
> before, but in this case as I mentioned, I need to first do a select
> from a table to get a max value, store that in a variable and then use
> that variable in a create sequence sql statement.

Something like this, off the top of my head and untested:

create or replace function make_sequence_for_table()
returns void
as
$$
declare
  max_value integer;
begin
  select max(my_column) into max_value from my_table;

   create sequence my_sequence
start (max_value + 1)
owned by my_table.my_column;

  alter table my_table
alter column my_column
set default nextval('my_sequence');

  return;
end;
$$
language plpgsql;


For extra marks, pass the table name in as a parameter, construct the
SQL as a string and execute it using EXECUTE (the pl/pgsql version, not
the command for executing prepared statements).

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] how to create a sequence in a stored proc?

[Merlin Moncure]

On Fri, Apr 27, 2012 at 11:35 AM, J.V.  wrote:
> Right, I understand that fully, and have used SQL inside a stored proc
> before, but in this case as I mentioned, I need to first do a select from a
> table to get a max value, store that in a variable and then use that
> variable in a create sequence sql statement.
>
> so I need to construct a string that contains the create sequence statement
> and execute that string, at least this is the way it is done in Oracle.
>
> I do not know how to use a variable in a create sequence statement in
> PostgreSQL.

oh I see. try this:


postgres=# do
$$
declare
  s int default 3;
begin
  execute 'create sequence v start ' ||  s;
end;
$$ language plpgsql;
DO

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] empty role names in pg_dumpall output

[Filip Rembiałkowski]

On Wed, Apr 25, 2012 at 8:56 PM, Tom Lane  wrote:
> =?UTF-8?Q?Filip_Rembia=C5=82kowski?=  writes:
>> PostgreSQL 9.0.4
>
>> I have this in pg_dumpall -g output (non-empty role names changed):
>
>> GRANT "" TO a  GRANTED BY postgres;
>> GRANT "" TO b GRANTED BY c;
>> GRANT "" TO b GRANTED BY c;
>> GRANT "" TO b GRANTED BY c;
>> GRANT "" TO b GRANTED BY c;
>> GRANT "" TO "" GRANTED BY c;
>> GRANT "" TO "" GRANTED BY postgres;
>> GRANT "" TO "" GRANTED BY postgres;
>
> Hmm.  A look at the code in pg_dumpall suggests that the problem is
> unmatched entries in pg_auth_members, ie this query:
>
> SELECT ur.rolname AS roleid
> FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid
>
> is returning some null results.

Yes that is the case:

SELECT ur.rolname AS roleid, member, grantor
FROM pg_auth_members a
LEFT JOIN pg_authid ur on ur.oid = a.roleid
WHERE ur.oid IS NULL;

 roleid | member | grantor
++-
  |  21468 |   19553
  |  21468 |   19553
  |  18332 |   19553
  |  21468 |   19553
  |  18332 |  10
  |  20615 |  10
  |  18332 |  10
  |  21468 |   19553
(8 rows)


> You might look into that catalog
> and see if you can figure out what happened.
>

Could it be (theoretically) caused by human-made insertions into
pg_auth_members?

Maybe you remember some bug which could have caused this in the past?



Thanks alot for help,
Filip

-- 
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 a sequence in a stored proc?

[J.V.]

Right, I understand that fully, and have used SQL inside a stored proc 
before, but in this case as I mentioned, I need to first do a select 
from a table to get a max value, store that in a variable and then use 
that variable in a create sequence sql statement.


so I need to construct a string that contains the create sequence 
statement and execute that string, at least this is the way it is done 
in Oracle.


I do not know how to use a variable in a create sequence statement in 
PostgreSQL.


J.V.

On 4/27/2012 9:51 AM, Merlin Moncure wrote:

On Fri, Apr 27, 2012 at 10:37 AM, J.V.  wrote:

I need to create a sequence in a stored procedure.

First I need to select a value from a table and set the sequence start value
to that value.

We have a table that does not have a sequence on it, so I want to select the
max value, increment by one
and then start the sequence there.

We have to do this on three databases, I have figured out how to do this in
Oracle (build a string and the EXECUTE IMMEDIATE, but have not
figured out how to do this with PostgreSQL.

just about any sql is allowed from within a function, including CREATE
SEQUENCE.  functions can even create functions and execute them.

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] how to create a sequence in a stored proc?

[Merlin Moncure]

On Fri, Apr 27, 2012 at 10:37 AM, J.V.  wrote:
> I need to create a sequence in a stored procedure.
>
> First I need to select a value from a table and set the sequence start value
> to that value.
>
> We have a table that does not have a sequence on it, so I want to select the
> max value, increment by one
> and then start the sequence there.
>
> We have to do this on three databases, I have figured out how to do this in
> Oracle (build a string and the EXECUTE IMMEDIATE , but have not
> figured out how to do this with PostgreSQL.

just about any sql is allowed from within a function, including CREATE
SEQUENCE.  functions can even create functions and execute them.

merlin

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

[GENERAL] how to create a sequence in a stored proc?

[J.V.]

I need to create a sequence in a stored procedure.

First I need to select a value from a table and set the sequence start 
value to that value.


We have a table that does not have a sequence on it, so I want to select 
the max value, increment by one

and then start the sequence there.

We have to do this on three databases, I have figured out how to do this 
in Oracle (build a string and the EXECUTE IMMEDIATE , but have 
not figured out how to do this with PostgreSQL.


any ideas?

thanks


J.V.

--
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 set up automatically startup database when the server boot or reboot.

[David Johnston]

FYI: While is won’t impact this particular response the 8.2 release is no
longer supported.

 

Also, Linux has various flavors and while they are similar in many ways the
distribution that you are using is good information to provide.  

 

I will assume you installed PostgreSQL via a package manager but you should
also state that explicitly.

 

Dave

 

 

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of yxj
Sent: Friday, April 27, 2012 10:46 AM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to set up automatically startup database when the
server boot or reboot.

 

David, Thanks for your reminder. My database version is 8.2.15. And My os
platform is Linux 5.5.  Thanks I really appreciate it. Grace

 


At 2012-04-27 22:36:51,"David Johnston"  wrote:
>PostgreSQL runs on numerous operating systems.  If you do not specify which
one you are using, as well as how you went about installing PostgreSQl, no
one is going to be able to help you.  The  PostgreSQL version would probably
help as well.
> 
>David J.
> 
>On Apr 27, 2012, at 10:26, leaf_yxj  wrote:
> 
>> My company want to setup automatically startup database. For oracle
database,
>> there is a bullitin script and configuration of auto start when the
server
>> boot or reboot? Hi Guys, Please help. Thanks. Regards.Grace
>> 
>> --
>> View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-
database-when-the-server-boot-or-reboot-tp5670442p5670442.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>> 
>> -- 
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general

 

  _  

  网易Lofter，专注兴趣，分享创作！ 

Re: [GENERAL] Re: how to set up automatically startup database when the server boot or reboot.

[Alban Hertroys]

On 27 April 2012 16:47, leaf_yxj  wrote:
> David, Thanks for your reminder. My database version is 8.2.15. And My os
> platform is Linux 5.5.  Thanks I really appreciate it. Grace

There is no such thing as Linux 5.5.

But since you're on _a_ Linux distribution (there are many), you'll
probably find some script in /etc/init.d/ that's used to start the DB
at boot. Check the documentation of your OS for how to go about that.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

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

[GENERAL] Re: how to set up automatically startup database when the server boot or reboot.

[leaf_yxj]

David, Thanks for your reminder. My database version is 8.2.15. And My os
platform is Linux 5.5.  Thanks I really appreciate it. Grace


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670504.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Re: how to set up automatically startup database when the server boot or reboot.

[leaf_yxj]

David, Thanks for your reminder. My database version is 8.2.15. And My os 
platform is Linux 5.5.  Thanks I really appreciate it. Grace

 


At 2012-04-27 22:38:11,"David Johnston [via PostgreSQL]" 
 wrote:
PostgreSQL runs on numerous operating systems.  If you do not specify which one 
you are using, as well as how you went about installing PostgreSQl, no one is 
going to be able to help you.  The  PostgreSQL version would probably help as 
well.

David J.

On Apr 27, 2012, at 10:26, leaf_yxj <[hidden email]> wrote:


> My company want to setup automatically startup database. For oracle database,
> there is a bullitin script and configuration of auto start when the server
> boot or reboot? Hi Guys, Please help. Thanks. Regards.Grace
>
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670442.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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



If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670481.html
To unsubscribe from how to set up automatically startup database when the 
server boot or reboot., click here.
NAML

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670496.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] how to set up automatically startup database when the server boot or reboot.

[yxj]

David, Thanks for your reminder. My database version is 8.2.15. And My os 
platform is Linux 5.5.  Thanks I really appreciate it. Grace





At 2012-04-27 22:36:51,"David Johnston"  wrote:
>PostgreSQL runs on numerous operating systems.  If you do not specify which 
>one you are using, as well as how you went about installing PostgreSQl, no one 
>is going to be able to help you.  The  PostgreSQL version would probably help 
>as well.
>
>David J.
>
>On Apr 27, 2012, at 10:26, leaf_yxj  wrote:
>
>> My company want to setup automatically startup database. For oracle database,
>> there is a bullitin script and configuration of auto start when the server
>> boot or reboot? Hi Guys, Please help. Thanks. Regards.Grace
>> 
>> --
>> View this message in context: 
>> http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670442.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to set up automatically startup database when the server boot or reboot.

[David Johnston]

PostgreSQL runs on numerous operating systems.  If you do not specify which one 
you are using, as well as how you went about installing PostgreSQl, no one is 
going to be able to help you.  The  PostgreSQL version would probably help as 
well.

David J.

On Apr 27, 2012, at 10:26, leaf_yxj  wrote:

> My company want to setup automatically startup database. For oracle database,
> there is a bullitin script and configuration of auto start when the server
> boot or reboot? Hi Guys, Please help. Thanks. Regards.Grace
> 
> --
> View this message in context: 
> http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670442.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> 
> -- 
> 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

[GENERAL] How to get the all the activities running during specific time range.Thanks!Plese help!

[leaf_yxj]

I was asked to run a report to my boss all the activities running during a
specific time range. I want to create a script to run it. I know I can query
from  pg_stat_activity for my previous postgres version. But I can't find
this system table in 8.2.15. Please help. Thanks. Regards. Grace.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-get-the-all-the-activities-running-during-specific-time-range-Thanks-Plese-help-tp5670460p5670460.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to set up automatically startup database when the server boot or reboot.

[leaf_yxj]

My company want to setup automatically startup database. For oracle database,
there is a bullitin script and configuration of auto start when the server
boot or reboot? Hi Guys, Please help. Thanks. Regards.Grace

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-set-up-automatically-startup-database-when-the-server-boot-or-reboot-tp5670442p5670442.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] postgresql standby using pg_archivecleanup don't work

[leo xu]

i have one parimary ,two standby. one standby using stream replication
connect to primary.another standby using basebackup,then recovery database
--cp archivelog from primary using crontab.i want to delete old archivelog
from standby.but configure pg_archivecleanup don't work,meantime no error in
postgresql log.postgresql version is 9.1.2.
   the following is my recovery.conf :
   This is my recovery.conf:

standby_mode = 'on'
restore_command = 'cp /attend_pg_backup/u02/pg9.1/archivelog/%f %p'
archive_cleanup_command ='pg_archivecleanup
/attend_pg_backup/u02/pg9.1/archivelog %r'



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/postgresql-standby-using-pg-archivecleanup-don-t-work-tp5669340p5669340.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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 speed of 2 functions: SQL+STABLE and PLPGSQL+EXECUTE

[Albe Laurenz]

Dmitry Koterov wrote:
> For example, I have 2 functions like these:
> 
> CREATE OR REPLACE FUNCTION first(a INTEGER, b INTEGER, ...) RETURNS
... AS
> $body$
> ...any SQL which uses $1, $2 etc. arguments, plus LIMIT $3...
> $body$
> LANGUAGE 'sql'
> STABLE
> 
> 
> and
> 
> 
> CREATE OR REPLACE FUNCTION second(a INTEGER, b INTEGER, ...) RETURNS
... AS
> $body$
> DECLARE
> res ...;
> BEGIN
> EXECUTE '...the same SELECT, ' ||
> 'but ' || quote_literal(a) || ' args are embedded, plus ' ||
> 'LIMIT ' || quote_literal($3)
> INTO res;
> RETURN res;
> END;
> $body$
> LANGUAGE 'plpgsql'
> STABLE
> 
> And then I call
> 
> EXPLAIN ANALYZE SELECT * FROM first(...);
> EXPLAIN ANALYZE SELECT * FROM second(...);
> 
> Should these two queries be executed by the same time usage (i.e. does
PostgreSQL generate same plans
> for inner queries)?
> 
> I always thought that the answer is YES: if a function is STABLE and
with language=SQL, its SQL code
> is embedded into outer context after all arguments are expanded into
their values (so the plan is
> built after argument expansion). But some days ago I detected a case
when second() works about 100
> times faster than first(), and the cause is seems that the planner
does not see all of expanded
> arguments in first() (if I replace arguments to constants in first(),
especially in LIMIT clause, it
> begins to work the same speed as second() does). Unfortunately EXPLAIN
ANALYZE does not go into
> functions and shows only overall time, so I have no real information
about what plan is actually used
> in first().

You can get EXPLAIN plans if you use the auto_explain contrib module
with auto_explain.log_nested_statements enabled.

As you suspect, the two functions work differently.
The SQL function will plan a parameterized statement (with $1 etc. in
it)
and execute that statement whenever it is called, while the PL/pgSQL
function will execute an SQL statement with all the constant literals
in it that gets planned and executed when you call the function.

The SQL function will create a statement that cannot benefit from
optimizations that work only for certain constant values (although
there will be improvements in 9.2 for that).  On the down side,
the PL/pgSQL function will have to plan the query every time it is
executed, which does not come for free.

To illustrate that, an example:

I create a table "test" as follows:

CREATE TABLE test(id integer PRIMARY KEY, val text NOT NULL);
CREATE INDEX test_val_ind ON test(val);

Then I fill it with 1000 rows, 11 of which have val='test'
and ANALYZE the table.

CREATE OR REPLACE FUNCTION first(text, integer) RETURNS integer
   STABLE STRICT LANGUAGE sql AS
   'SELECT id FROM test WHERE val=$1 LIMIT $2';

CREATE OR REPLACE FUNCTION second(IN v text, IN l integer, OUT r
integer)
   STABLE STRICT LANGUAGE plpgsql AS
   $$BEGIN
  EXECUTE 'SELECT id FROM test WHERE val=' || quote_literal(v)
 || ' LIMIT ' || CAST(l AS integer) INTO r;
   END$$;

Then I execute "SELECT first('test', 1)" and "SELECT SECOND('test', 1)".
The respective plans are:

For the SQL function:

Query Text: SELECT id FROM test WHERE val=$1 LIMIT $2
Limit  (cost=0.00..1.75 rows=50 width=4)
  ->  Seq Scan on test  (cost=0.00..17.50 rows=500 width=4)
Filter: (val = $1)

For the PL/pgSQL function:

Query Text: SELECT id FROM test WHERE val='test' LIMIT 1
Limit  (cost=0.00..0.95 rows=1 width=4)
  ->  Index Scan using test_val_ind on test  (cost=0.00..10.46 rows=11
width=4)
Index Cond: (val = 'test'::text)

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] R-tree parallel index creation

[Albe Laurenz]

Pavel Iacovlev wrote:
> Anyone know if this features is supported in PostgreSQL:
> "R-tree index creation can be subdivided into smaller tasks that can be 
> performed in parallel" ?

The PostgreSQL backend does not parallelize anything, including index builds.

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

[GENERAL] Is it possible to call other functions inside plpythonu?

[Frank Lanitz]

Hi folks,

Just looking for a nice server side solution to implement some
fundamental logic for an application. plpythonu looks in this tmers very
well as I'm liking the syntax of Python. However, an very old blog post
at [1] made me unsure whether really to use it. Is it still (or has it
ever been) an issue that plpythonu is having a lot of overhead and not
able to make use of other functions? Didn't found anything on docu for
9.1 about that.

Cheers,
Frank

[1] http://spyced.blogspot.de/2005/04/plpython-intro.html

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