Re: [GENERAL] main log encoding problem
On 05/23/2012 09:15 AM, yi huang wrote: I'm using postgresql 9.1.3 from debian squeeze-backports with zh_CN.UTF-8 locale, i find my main log (which is "/var/log/postgresql/postgresql-9.1-main.log") contains "???" which indicate some sort of charset encoding problem. It's a known issue, I'm afraid. The PostgreSQL postmaster logs in the system locale, and the PostgreSQL backends log in whatever encoding their database is in. They all write to the same log file, producing a log file full of mixed encoding data that'll choke many text editors. If you force your editor to re-interpret the file according to the encoding your database(s) are in, this may help. In the future it's possible that this may be fixed by logging output to different files on a per-database basis or by converting the text encoding of log messages, but no agreement has been reached on the correct approach and nobody has stepped up to implement it. -- 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] Disable Streaming Replication without restarting either master or slave
Hi, Is it possible stop/disable streaming replication without stopping or restarting either master or slave servers? Since stopping or restarting the postgres servers would involve complete invalidation of the connection pool [Java/JEE app server pool] that may take a few minutes before the application becomes usable, it would be great if there is a way we can disable replication [for maintenance reasons like applying patches or upgrades, etc]. Thanks and Regards, Samba
Re: [GENERAL] Export and import from one postgres server to another
On 05/28/2012 11:27 AM, Alexander Reichstadt wrote: Hi, I am trying to "simply" export my postgres database from one server and then import it into another. I thought I could use PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, the go to the other server, select the database, click the SQL button and then select the dump file. When I do this the import fails because the dump file begins with a \connect statement. The escape char doesn't seem to go through the web frontend or something. But without the connect statement it ends up telling me no database selected. Please, can someone let me know how to do this? From the phpPgAdmin FAQ: https://raw.github.com/phppgadmin/phppgadmin/master/FAQ Questions on dumps -- Q: Why can't I reload the SQL script I dumped in the SQL window? A: The following limitations currently exist in SQL script execution: * Only uploaded SQL scripts can contain COPY commands and for this to work, you must have PHP 4.2 or higher. * 'psql' commands such as '\connect' will not work at all. * Multiline SQL statements will not work, eg: CREATE TABLE example ( a INTEGER ); * You cannot change the current database or current user during the execution of the script. We do intend to work on some of these limitations in the future, but some of them are Postgres restrictions and we recommend using the 'psql' utility to restore your full SQL dumps. Thanks Alex -- 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] How to handle ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX with foreign keys
Hello, PostgreSQL 9.1 introduced an ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX command to help index maintenance. I works for some cases, but I can't get it work with a primary key index which is referenced by a foreign key. Here's an example of the problem I encounter : db=# CREATE TABLE master (i serial primary key, value integer); NOTICE: CREATE TABLE will create implicit sequence "master_i_seq" for serial column "master.i" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master" CREATE TABLE db=# CREATE TABLE detail (id serial primary key, master_id integer REFERENCES master (i)); NOTICE: CREATE TABLE will create implicit sequence "detail_id_seq" for serial column "detail.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "detail_pkey" for table "detail" CREATE TABLE db=# CREATE UNIQUE INDEX CONCURRENTLY tmp_index ON master (i); CREATE INDEX And now, we try to drop the old PK constraint and create a new one with index tmp_index, which fails because of the FK : db=# ALTER TABLE master DROP CONSTRAINT master_pkey, ADD CONSTRAINT master_pkey PRIMARY KEY USING INDEX tmp_index; ERROR: cannot drop constraint master_pkey on table master because other objects depend on it DETAIL: constraint detail_master_id_fkey on table detail depends on index master_pkey HINT: Use DROP ... CASCADE to drop the dependent objects too. Actually, the only way to "solve" this issue is to swap the relfilenode columns between the old and the new index. I don't like this option very much by the way but I'm very interested by your opinion on updating the pg_class catalog this way. Kind regards, Thomas Reiss -- 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] Disable Streaming Replication without restarting either master or slave
Hi! Le 2012-05-29 à 06:18, Samba a écrit : > Is it possible stop/disable streaming replication without stopping or > restarting either master or slave servers? > > Since stopping or restarting the postgres servers would involve complete > invalidation of the connection pool [Java/JEE app server pool] that may take > a few minutes before the application becomes usable, it would be great if > there is a way we can disable replication [for maintenance reasons like > applying patches or upgrades, etc]. Are per-chance looking for pg_xlog_replay_pause() and pg_xlog_replay_resume() ? http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE Hope that helps! François Beausoleil
Re: [GENERAL] How to handle ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX with foreign keys
On 05/29/2012 06:08 AM, Thomas Reiss wrote: Hello, PostgreSQL 9.1 introduced an ALTER TABLE DROP CONSTRAINT, ADD CONSTRAINT USING INDEX command to help index maintenance. I works for some cases, but I can't get it work with a primary key index which is referenced by a foreign key. Here's an example of the problem I encounter : db=# CREATE TABLE master (i serial primary key, value integer); NOTICE: CREATE TABLE will create implicit sequence "master_i_seq" for serial column "master.i" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master" CREATE TABLE db=# CREATE TABLE detail (id serial primary key, master_id integer REFERENCES master (i)); NOTICE: CREATE TABLE will create implicit sequence "detail_id_seq" for serial column "detail.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "detail_pkey" for table "detail" CREATE TABLE db=# CREATE UNIQUE INDEX CONCURRENTLY tmp_index ON master (i); CREATE INDEX And now, we try to drop the old PK constraint and create a new one with index tmp_index, which fails because of the FK : db=# ALTER TABLE master DROP CONSTRAINT master_pkey, ADD CONSTRAINT master_pkey PRIMARY KEY USING INDEX tmp_index; ERROR: cannot drop constraint master_pkey on table master because other objects depend on it DETAIL: constraint detail_master_id_fkey on table detail depends on index master_pkey HINT: Use DROP ... CASCADE to drop the dependent objects too. Actually, the only way to "solve" this issue is to swap the relfilenode columns between the old and the new index. I don't like this option very much by the way but I'm very interested by your opinion on updating the pg_class catalog this way. Why not?: BEGIN; ALTER TABLE master DROP CONSTRAINT master_pkey CASCADE, ADD CONSTRAINT master_pkey PRIMARY KEY USING INDEX tmp_index; ALTER TABLE detail ADD CONSTRAINT detail_master_id_fkey FOREIGN KEY(master_id) REFERENCES master(id); COMMIT; Though I am not sure what the above gets you as there is already an index on master.id. Kind regards, Thomas Reiss -- 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
Re: [GENERAL] PG vs MSSQL language comparison ?
On 27/05/2012 04:46, F. BROUARD / SQLpro wrote: >> It's a little out of date (I've been meaning to update it for about 2 years >> now) and has some gaps, but try the Database Rosetta Stone. >> >> http://www.grantondata.com/community/dbrosettastone.html > > Very out of date... Yep, agreed, as you found with the examples you shared :) Thanks for those, it's motivation to get my act in gear and bring it up to speed for 9.2. :) -- 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] Export and import from one postgres server to another
On 29 May 2012 14:58, Adrian Klaver wrote: > Q: Why can't I reload the SQL script I dumped in the SQL window? > > A: The following limitations currently exist in SQL script execution: > > * 'psql' commands such as '\connect' will not work at all. Wait a minute! They use the pg_dump utility to create the dump file (there are \connect statements and such in it, after all), but they don't use psql/pg_restore to restore? -- 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
Re: [GENERAL] Export and import from one postgres server to another
On Mon, May 28, 2012 at 1:27 PM, Alexander Reichstadt wrote: > Hi, > > I am trying to "simply" export my postgres database from one server and then > import it into another. I thought I could use PhpPgAdmin, but the hints on > the web don't seem to work. One said to simply get a dump from one > phpPgAdmin, the go to the other server, select the database, click the SQL > button and then select the dump file. When I do this the import fails because > the dump file begins with a \connect statement. The escape char doesn't seem > to go through the web frontend or something. But without the connect > statement it ends up telling me no database selected. Please, can someone let > me know how to do this? command line tools and simple scripting are the way to do this. it works equally well on *nix and windows. a simple script might be: psql -h restoredb -c "drop database if exists mydb" psql -h restoredb -c "create database mydb" pg_dump -h dumpdb | psql restoredb you'll probably want to add some logging and error checking (this will work differently depending on o/s) but that's the basic mechanism. Once you have it scripted, it then becomes a matter of scheduling the backup and restore. Another fancier approach to dealing with this is to look at setting up replication. 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] Disable Streaming Replication without restarting either master or slave
On Tue, May 29, 2012 at 7:18 PM, Samba wrote: > Hi, > > > Is it possible stop/disable streaming replication without stopping or > restarting either master or slave servers? > > Since stopping or restarting the postgres servers would involve complete > invalidation of the connection pool [Java/JEE app server pool] that may take > a few minutes before the application becomes usable, it would be great if > there is a way we can disable replication [for maintenance reasons like > applying patches or upgrades, etc]. There is no clean way to disable streaming replication. But you can do that by the following steps: 1. change pg_hba.conf in the master so that the master does not accept new replication connection 2. reload pg_hba.conf in the master 3. send SIGTERM signal to currently-running walsender process, e.g., by "select pg_terminate_backend(pid) from pg_stat_replication". Then replication connection will be terminated. The standby tries reconnecting to the master, but which will continue failing until you'll change pg_hba.conf again. Regards, -- Fujii Masao -- 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] Disable Streaming Replication without restarting either master or slave
On Tue, May 29, 2012 at 10:17 PM, François Beausoleil wrote: > Hi! > > Le 2012-05-29 à 06:18, Samba a écrit : > > Is it possible stop/disable streaming replication without stopping or > restarting either master or slave servers? > > Since stopping or restarting the postgres servers would involve complete > invalidation of the connection pool [Java/JEE app server pool] that may take > a few minutes before the application becomes usable, it would be great if > there is a way we can disable replication [for maintenance reasons like > applying patches or upgrades, etc]. > > > Are per-chance looking for pg_xlog_replay_pause() and > pg_xlog_replay_resume() ? Those can pause and resume WAL replay in the standby, but not streaming replication. Even while WAL replay is being paused, WAL can be streamed from the master to the standby. Regards, -- Fujii Masao -- 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] Disable Streaming Replication without restarting either master or slave
On Tue, May 29, 2012 at 2:18 PM, Samba wrote: > Is it possible stop/disable streaming replication without stopping or > restarting either master or slave servers? > > Since stopping or restarting the postgres servers would involve complete > invalidation of the connection pool [Java/JEE app server pool] that may take > a few minutes before the application becomes usable, it would be great if > there is a way we can disable replication [for maintenance reasons like > applying patches or upgrades, etc]. It is not really clear what is wrong with just stopping the replication server for maintenance while keeping the master working? > > > Thanks and Regards, > Samba -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] Disable Streaming Replication without restarting either master or slave
-- Forwarded message -- From: Michael Nolan Date: Tue, May 29, 2012 at 1:37 PM Subject: Re: [GENERAL] Disable Streaming Replication without restarting either master or slave To: Fujii Masao On Tue, May 29, 2012 at 1:15 PM, Fujii Masao wrote: > On Tue, May 29, 2012 at 10:17 PM, François Beausoleil > wrote: > > > Are per-chance looking for pg_xlog_replay_pause() and > > pg_xlog_replay_resume() ? > > Those can pause and resume WAL replay in the standby, but not streaming > replication. Even while WAL replay is being paused, WAL can be streamed > from the master to the standby. > > Regards, > > -- > Fujii Masao > So, that means that the only ways to stop streaming replication are to stop the slave server, to disable access to the master via the pg_hba.conf file (requiring the master configs be reloaded) or to set the trigger file on the slave to tell it to stop replicating the master. And if the master/slave are set to synchronous streaming replication, your options are more limited, since the master has to know to stop waiting for the synchronous slave to respond. Once the slave has gone out of asynchronous replication mode, wuld it be possible to resume asynchronous replication by stopping the slave server, removing the trigger file, and restarting it in asynchronous streaming replication mode? This would, at a minimum, depend on how many updates have occurred on the master during the time streaming replication was disabled and having all the WAL files available, right? -- Mike Nolan
[GENERAL] Interval Division Workaround Suggestions (sub-day intervals only)?
Any suggestions on how to obtain the number of "X minute" intervals in "Y" where "Y" is always less than 24 hours? e.g., : '05:00:00'::interval / '00:06:00'::interval => 50 (integer) I am guessing because intervals cover not-well-defined (variable) periods of times (i.e., months, years) that the definition of "divided into" is not stable enough implement a general division operator for. >From the Wiki: http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL 4. Multiplication and division of INTERVALS is under development and discussion at this time It is suggested that you avoid it until implementation is complete or you may get unexpected results. Thanks! David J.
Re: [GENERAL] Interval Division Workaround Suggestions (sub-day intervals only)?
On Wed, May 30, 2012 at 3:50 AM, David Johnston wrote:
> Any suggestions on how to obtain the number of “X minute” intervals in “Y”
> where “Y” is always less than 24 hours?
>
> e.g., : ‘05:00:00’::interval / ’00:06:00’::interval => 50 (integer)
Turn them into integer seconds:
select
date_part('epoch','05:00:00'::interval)/date_part('epoch','00:06:00'::interval);
ChrisA
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disable Streaming Replication without restarting either master or slave
> > > Since stopping or restarting the postgres servers would involve complete > > invalidation of the connection pool [Java/JEE app server pool] that may > take > > a few minutes before the application becomes usable, it would be great if > > there is a way we can disable replication [for maintenance reasons like > > applying patches or upgrades, etc]. > > I think even applying patches or upgrades needs restart. > 3. send SIGTERM signal to currently-running walsender process, e.g., by >"select pg_terminate_backend(pid) from pg_stat_replication". Will it be helpful here sending SIGINT instead of killing ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Disable Streaming Replication without restarting either master or slave
On Wed, May 30, 2012 at 2:38 AM, Michael Nolan wrote: > > > -- Forwarded message -- > From: Michael Nolan > Date: Tue, May 29, 2012 at 1:37 PM > Subject: Re: [GENERAL] Disable Streaming Replication without restarting > either master or slave > To: Fujii Masao > > > > > On Tue, May 29, 2012 at 1:15 PM, Fujii Masao wrote: >> >> On Tue, May 29, 2012 at 10:17 PM, François Beausoleil >> wrote: > > >> >> > Are per-chance looking for pg_xlog_replay_pause() and >> > pg_xlog_replay_resume() ? >> >> Those can pause and resume WAL replay in the standby, but not streaming >> replication. Even while WAL replay is being paused, WAL can be streamed >> from the master to the standby. >> >> Regards, >> >> -- >> Fujii Masao > > > So, that means that the only ways to stop streaming replication are to stop > the slave server, to disable access to the master via the pg_hba.conf file > (requiring the master configs be reloaded) or to set the trigger file on the > slave to tell it to stop replicating the master. > > And if the master/slave are set to synchronous streaming replication, your > options are more limited, since the master has to know to stop waiting for > the synchronous slave to respond. > > Once the slave has gone out of asynchronous replication mode, wuld it be > possible to resume asynchronous replication by stopping the slave server, > removing the trigger file, and restarting it in asynchronous streaming > replication mode? This would, at a minimum, depend on how many updates have > occurred on the master during the time streaming replication was disabled > and having all the WAL files available, right? You'd like to restart the *promoted* standby server as the standby again? To do this, a fresh base backup must be taken from the master onto the standby before restarting it, even if there has been no update since the standby had been promoted. Regards, -- Fujii Masao -- 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] Disable Streaming Replication without restarting either master or slave
On Wed, May 30, 2012 at 3:04 AM, Raghavendra wrote: >> > Since stopping or restarting the postgres servers would involve complete >> > invalidation of the connection pool [Java/JEE app server pool] that may >> > take >> > a few minutes before the application becomes usable, it would be great >> > if >> > there is a way we can disable replication [for maintenance reasons like >> > applying patches or upgrades, etc]. >> > > I think even applying patches or upgrades needs restart. Yep. >> 3. send SIGTERM signal to currently-running walsender process, e.g., by >> "select pg_terminate_backend(pid) from pg_stat_replication". > > > Will it be helpful here sending SIGINT instead of killing ? No, walsender ignores SIGINT signal. Regards, -- Fujii Masao -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] full text searching
I have read the pg 9.1 doc's about full text search, rambled about the web a bit looking for related articles, and had good experiences implementing lightweight prototypes. Now I can sense the depth of the water as I try to determine a solid approach for implementation. I would be most grateful for links to articles that discuss the design issues one should consider before implementing a long term strategy. Case study, or architectural articles, and similar. Thanks, - Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Escaping `psql --variable`
Surprised that this works: echo ":foo" | psql --variable foo="SELECT 1 AS FOO;" template1 Why doesn't `psql` escape parameters passed in through `--variable`. When I use a library in other languages, they will escape the variable. How do I use `psql` from `bash` so that it will escape variables and thwart SQL injection? -- Alan Gutierrez - @bigeasy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updateable Views or Synonyms.
I am wondering if either of these features are on the plate for postgres anytime soon? I see conversations going back to 2007 on updateable views and some conversations about synonyms but obviously they have never been added to the database for some reason or another. With regards to synonyms. It seems to me I could kind of achieve the same functionality by creating a dblink into the same database. Would that be an insane? -- 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] timestamps, formatting, and internals
On 5/27/12 12:25 AM, Jasen Betts wrote: The query: "show integer_datetimes;" should return 'on' which means timestamps are microsecond precision if it returns 'off' your database was built with floating point timstamps and equality tests will be unreliable, I find that rather interesting. I was told that I was losing microseconds when I extracted an epoch from the difference between two timestamps and casted that value to an integer. So if I have integer timestamps ( your case above ) I get microseconds, but integer epochs is without microseconds? Thanks, -ds -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] select current_setting('transaction_isolation')
Howdy,
I recently did a log_min_duration_statement=0 run on my app, and found
~3million copies of
"select current_setting('transaction_isolation')"
I'm a Java + Hibernate stack. Does anyone know if this is a Hibernate artifact?
or a jdbc artifact?
or something else (implicit to some query pattern or trigger)?
Thanks
Dave
--
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] Updateable Views or Synonyms.
On Wed, 2012-05-30 at 11:16 +1200, Tim Uckun wrote: > I am wondering if either of these features are on the plate for > postgres anytime soon? I see conversations going back to 2007 on > updateable views and some conversations about synonyms but obviously > they have never been added to the database for some reason or another. Neither of these has active development right now, as far as I know. Updatable views will appear sometime, I'm sure, but I don't know when. Synonyms sound fairly simple, but I believe there are some concerns around catalog bloat and catalog lookup time. I can't remember the details. > With regards to synonyms. It seems to me I could kind of achieve the > same functionality by creating a dblink into the same database. Would > that be an insane? You'd be working outside of the transaction, so it seems like you're losing a lot there. How does it help you? Regards, Jeff Davis -- 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] Escaping `psql --variable`
On Tue, 2012-05-29 at 18:32 -0400, Alan Gutierrez wrote: > Surprised that this works: > > echo ":foo" | psql --variable foo="SELECT 1 AS FOO;" template1 > > Why doesn't `psql` escape parameters passed in through `--variable`. When I > use > a library in other languages, they will escape the variable. > > How do I use `psql` from `bash` so that it will escape variables and thwart > SQL > injection? http://www.postgresql.org/docs/9.1/static/app-psql.html#APP-PSQL-VARIABLES In particular, look at the section on SQL Interpolation. Hopefully that answers your question. Regards, Jeff Davis -- 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] Export and import from one postgres server to another
On 05/29/2012 02:27 AM, Alexander Reichstadt wrote: Hi, I am trying to "simply" export my postgres database from one server and then import it into another. I thought I could use PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, the go to the other server, select the database, click the SQL button and then select the dump file. When I do this the import fails because the dump file begins with a \connect statement. I don't really know why pg_dump includes that \connect , but it isn't the only problem you'll hit with that approach. PgAdmin-III seems to wrap the invoked SQL in a transaction, and the dump will try to (re)-create the `postgres' role, which will fail and abort the transaction. The usual way to dump and restore is to use `pg_dump -Fc -f mydb.backup mydb' when backing up, then either PgAdmin-III's "Restore" dialog or `pg_restore --dbname mydb mydb.backup' when restoring. You'll have to create the database prior to restoring to it. I wrote a bit of a grumble about the usability issues around dump/reload especially between *nix and Windows recently: http://blog.ringerc.id.au/2012/05/postgresql-usability-pgadmin-iii-and-pg.html and would be interested in your comments/experiences because more knowledge will help produce a better UI if I ever get the time to have a go at addressing some of this, or if someone else does. -- Craig Ringer
Re: [GENERAL] Export and import from one postgres server to another
On 05/29/2012 06:56 PM, Craig Ringer wrote: On 05/29/2012 02:27 AM, Alexander Reichstadt wrote: Hi, I am trying to "simply" export my postgres database from one server and then import it into another. I thought I could use PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, the go to the other server, select the database, click the SQL button and then select the dump file. When I do this the import fails because the dump file begins with a \connect statement. I don't really know why pg_dump includes that \connect , but it isn't the only problem you'll hit with that approach. PgAdmin-III seems to wrap the invoked SQL in a transaction, and the dump will try to (re)-create the `postgres' role, which will fail and abort the transaction. Well just to keep the confusion level down a bit, the OP was referring to phpPgAdmin:) -- Craig Ringer -- 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
Re: [GENERAL] timestamps, formatting, and internals
On 05/29/2012 04:28 PM, David Salisbury wrote: > > > On 5/27/12 12:25 AM, Jasen Betts wrote: >> The query: "show integer_datetimes;" should return 'on' which means >> timestamps are microsecond precision if it returns 'off' your database >> was built with floating point timstamps and equality tests will be >> unreliable, > > I find that rather interesting. I was told that I was losing microseconds > when I extracted an epoch from the difference between two timestamps and > casted > that value to an integer. So if I have integer timestamps ( your case > above ) > I get microseconds, but integer epochs is without microseconds? test=> SELECT extract(epoch from(now() - (now() - interval '1.345577 sec'))); date_part --- 1.345577 test=> SELECT extract(epoch from(now() - (now() - interval '1.345577 sec')))::int; date_part --- 1 An integer is an integer so you will lose all the fractional parts:) > > Thanks, > > -ds > -- 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
Re: [GENERAL] Escaping `psql --variable`
On Tue, May 29, 2012 at 05:19:49PM -0700, Jeff Davis wrote: > On Tue, 2012-05-29 at 18:32 -0400, Alan Gutierrez wrote: > > Surprised that this works: > > > > echo ":foo" | psql --variable foo="SELECT 1 AS FOO;" template1 > > > > Why doesn't `psql` escape parameters passed in through `--variable`. When I > > use > > a library in other languages, they will escape the variable. > > > > How do I use `psql` from `bash` so that it will escape variables and thwart > > SQL > > injection? > > http://www.postgresql.org/docs/9.1/static/app-psql.html#APP-PSQL-VARIABLES > > In particular, look at the section on SQL Interpolation. Hopefully that > answers your question. > > Regards, > Jeff Davis > Yes. Thank you. To escape a variable and thwart SQL injection: cat
[GENERAL] How to handle nested record data.
I'm porting a oracle function to postgresql, which has signature like this: FUNCTION foo ( seq IN varchar , somerow OUT SomeTable , otherinfo OUT varchar ) It's easy to port this function itself to postgresql, but i have problem to execute this function and assign the results into variables: SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo); It complains v_somerow can not be row type. How to handle the result of function foo? Best regards. YiHuang.
Re: [GENERAL] How to handle nested record data.
Hello
create or replace function call_foo()
returns void as $$
declare r record;
begin
r := foo('Hello');
raise notice ''% %', r.somerow, r.otherinfo;
end;
$$ language plpgsql;
regards
Pavel
2012/5/30 yi huang :
> I'm porting a oracle function to postgresql, which has signature like this:
>
> FUNCTION foo
> ( seq IN varchar
> , somerow OUT SomeTable
> , otherinfo OUT varchar
> )
>
> It's easy to port this function itself to postgresql, but i have problem to
> execute this function and assign the results into variables:
>
> SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo);
>
> It complains v_somerow can not be row type.
>
> How to handle the result of function foo?
>
> Best regards.
> YiHuang.
--
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] Export and import from one postgres server to another
On 05/30/2012 10:01 AM, Adrian Klaver wrote: On 05/29/2012 06:56 PM, Craig Ringer wrote: On 05/29/2012 02:27 AM, Alexander Reichstadt wrote: Hi, I am trying to "simply" export my postgres database from one server and then import it into another. I thought I could use PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, the go to the other server, select the database, click the SQL button and then select the dump file. When I do this the import fails because the dump file begins with a \connect statement. I don't really know why pg_dump includes that \connect , but it isn't the only problem you'll hit with that approach. PgAdmin-III seems to wrap the invoked SQL in a transaction, and the dump will try to (re)-create the `postgres' role, which will fail and abort the transaction. Well just to keep the confusion level down a bit, the OP was referring to phpPgAdmin:) Argh, I missed that. Thanks very much for spotting and correcting it. -- 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 handle nested record data.
Thanks for you quick reply, but i need to take this returned row in another
pgsql function and do further processing.
On Wed, May 30, 2012 at 11:21 AM, Pavel Stehule wrote:
> Hello
>
> create or replace function call_foo()
> returns void as $$
> declare r record;
> begin
> r := foo('Hello');
> raise notice ''% %', r.somerow, r.otherinfo;
> end;
> $$ language plpgsql;
>
> regards
>
> Pavel
>
> 2012/5/30 yi huang :
> > I'm porting a oracle function to postgresql, which has signature like
> this:
> >
> > FUNCTION foo
> > ( seq IN varchar
> > , somerow OUT SomeTable
> > , otherinfo OUT varchar
> > )
> >
> > It's easy to port this function itself to postgresql, but i have problem
> to
> > execute this function and assign the results into variables:
> >
> > SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo);
> >
> > It complains v_somerow can not be row type.
> >
> > How to handle the result of function foo?
> >
> > Best regards.
> > YiHuang.
>
--
http://yi-programmer.com/
Re: [GENERAL] How to handle nested record data.
On Wed, May 30, 2012 at 12:36 PM, yi huang wrote:
> Thanks for you quick reply, but i need to take this returned row in
> another pgsql function and do further processing.
Sorry, i was misunderstood, i get it now, just use a record type, thanks
very much.
>
>
> On Wed, May 30, 2012 at 11:21 AM, Pavel Stehule
> wrote:
>
>> Hello
>>
>> create or replace function call_foo()
>> returns void as $$
>> declare r record;
>> begin
>> r := foo('Hello');
>> raise notice ''% %', r.somerow, r.otherinfo;
>> end;
>> $$ language plpgsql;
>>
>> regards
>>
>> Pavel
>>
>> 2012/5/30 yi huang :
>> > I'm porting a oracle function to postgresql, which has signature like
>> this:
>> >
>> > FUNCTION foo
>> > ( seq IN varchar
>> > , somerow OUT SomeTable
>> > , otherinfo OUT varchar
>> > )
>> >
>> > It's easy to port this function itself to postgresql, but i have
>> problem to
>> > execute this function and assign the results into variables:
>> >
>> > SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo);
>> >
>> > It complains v_somerow can not be row type.
>> >
>> > How to handle the result of function foo?
>> >
>> > Best regards.
>> > YiHuang.
>>
>
>
>
> --
> http://yi-programmer.com/
>
--
http://yi-programmer.com/
Re: [GENERAL] How to handle nested record data.
On Wed, May 30, 2012 at 11:21 AM, Pavel Stehule wrote:
> Hello
>
> create or replace function call_foo()
> returns void as $$
> declare r record;
> begin
> r := foo('Hello');
> raise notice ''% %', r.somerow, r.otherinfo;
> end;
> $$ language plpgsql;
>
It turns out i also need to define a type for the result record of `foo`,
because record can't reveal the structure of the result (it complains:
record "r" has no field "somerow").
I have to created this type:
create type foo_result as (somerow SomeTable, otherinfo varchar);
then change `r record;` to `r foo_result;` , no need change `foo` itself,
and it works now.
I don't know is this the best way to do this though.
>
> regards
>
> Pavel
>
> 2012/5/30 yi huang :
> > I'm porting a oracle function to postgresql, which has signature like
> this:
> >
> > FUNCTION foo
> > ( seq IN varchar
> > , somerow OUT SomeTable
> > , otherinfo OUT varchar
> > )
> >
> > It's easy to port this function itself to postgresql, but i have problem
> to
> > execute this function and assign the results into variables:
> >
> > SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo);
> >
> > It complains v_somerow can not be row type.
> >
> > How to handle the result of function foo?
> >
> > Best regards.
> > YiHuang.
>
--
http://yi-programmer.com/
Re: [GENERAL] How to handle nested record data.
2012/5/30 yi huang :
> On Wed, May 30, 2012 at 11:21 AM, Pavel Stehule
> wrote:
>>
>> Hello
>>
>> create or replace function call_foo()
>> returns void as $$
>> declare r record;
>> begin
>> r := foo('Hello');
>> raise notice ''% %', r.somerow, r.otherinfo;
>> end;
>> $$ language plpgsql;
>
>
> It turns out i also need to define a type for the result record of `foo`,
> because record can't reveal the structure of the result (it complains:
> record "r" has no field "somerow").
> I have to created this type:
>
> create type foo_result as (somerow SomeTable, otherinfo varchar);
>
> then change `r record;` to `r foo_result;` , no need change `foo` itself,
> and it works now.
>
> I don't know is this the best way to do this though.
best way is way that works :). Implementation of records and related
features is relative complex and not consistent all time - mainly when
function with OUT arguments is used. There are a issues - sometimes
cast is necessary, sometimes nested records can be accessed only to
first level (and for second levels you needs casts or auxiliary
variables) - so it works perfectly, but sometime is difficult to find
syntax that works.
Regards
Pavel
>
>>
>>
>> regards
>>
>> Pavel
>>
>> 2012/5/30 yi huang :
>> > I'm porting a oracle function to postgresql, which has signature like
>> > this:
>> >
>> > FUNCTION foo
>> > ( seq IN varchar
>> > , somerow OUT SomeTable
>> > , otherinfo OUT varchar
>> > )
>> >
>> > It's easy to port this function itself to postgresql, but i have problem
>> > to
>> > execute this function and assign the results into variables:
>> >
>> > SELECT (foo(seq)).* INTO (v_somerow, v_otherinfo);
>> >
>> > It complains v_somerow can not be row type.
>> >
>> > How to handle the result of function foo?
>> >
>> > Best regards.
>> > YiHuang.
>
>
>
>
> --
> http://yi-programmer.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
