Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3
Ok so guys CREATE or REPLACE FUNCTION function_data_1() RETURNS SETOF bigint AS $$ declare row record; BEGIN [...] FOR row IN EXECUTE ' SELECT t1.file_id, t1.path, t1.account_id FROM table1 t1 JOIN table3 t3 ON t3.file_Id = t1.file_id WHERE t3.migrated = 0 AND *t3.account_id = 1112* ORDER BY 1 LIMIT 30 ' [...] How can I make the function works with account_id? *Example: select function_data_1(1112)* and then it will do all the work just for that specific account_id? If you guys please could give me the way to do that.. thanks Patrick
Re: [GENERAL] [HACKERS] OUT parameter and RETURN table/setof
Existing application code written to call function in Oracle which return no.of rows in out parameter and return-values is cursor-result this need migrate to PostgreSQL, need help here example: (actual function declaration only) *Oracle:* CREATE OR REPLACE PROCEDURE sc_getapppermissionlist ( v_role_ids IN VARCHAR2, v_rowsfound OUT INTEGER, result_cursor1 OUT SYS_REFCURSOR ) ... *PostgreSQL:* *method 1*: CREATE OR REPLACE PROCEDURE sc_getapppermissionlist ( v_role_ids IN VARCHAR, v_rowsfound OUT INTEGER, result_cursor1 OUT REFCURSOR ) ... but this approach issue is, need to do in BEGIN - END block inside with FETCH ALL IN "" - here we need/think common approach for database *method 2:* CREATE OR REPLACE PROCEDURE sc_getapppermissionlist ( v_role_ids IN VARCHAR, v_rowsfound OUT INTEGER) RETURNS TABLE/SETOF ... this approach is not working Thanks Sridhar OpenText On Mon, Jun 6, 2016 at 5:57 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 6, 2016 at 7:17 AM, Sridhar N Bamandlapally < > sridhar@gmail.com> wrote: > >> Hi >> >> Is there any option in PGPLSQL which can RETURNS table or SETOF rows >> along with an OUT parameter? >> >> > No, there would be no point given the internals of how functions work. > > What is it you are trying to do? > > David J. > > >
Re: [GENERAL] Postgres Dropped DB have recovered files how to restore
On 6/6/2016 4:09 PM, Alvaro Herrera wrote: I have no idea about Windows filesystems but you may be able to "undelete" the files, as long as you don't touch the partition for anything else; search the web for "undelete ntfs". Once you undelete you will need to put them back in the right places. I suggest you get a disk with twice as much space as the original; make an image copy of the original disk and don't touch the original anymore. Then try to undelete the files from the image. Use the list Adrian provided as a guide for what you're missing. You*may* be able to recover the data, if you're lucky and careful. that won't work if he did a DROP DATABASE as all the metadata is gone. -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] Re-sync slave server
My set up: Master --> slave01 (streaming replication) --> slave02 (streaming replication) Master --> slave03 (wal_files 4 days old, not streaming replication) The wal_files are stored into each server. But when the slave03 was down, the wal_files weren't being copied into it. We took too long to discover that, and now we lost some wal_files.. they've been recycled... So my question is: Can I just do a RE-SYNC from slave01 to slave03 of the data folder? Or do I have to sync all the database again? I'm asking because the DB is 2.2TB, don't wanna do one step and discover later that did not work and have to do all over again Cheers Patrick
Re: [GENERAL] Re-sync slave server
On 06/06/2016 05:11 PM, Patrick B wrote: Hi guys, I'm using PostgreSQL 9.2 There is a backup server that is 4 days old replication by wal_files (not streaming) However, the server went down and I lost some wal_files.. and now the replication is not working... What is your set up?: standby server master --> | WAL archive --> standby_cluster OR wal storage standby server master --> | WAL archive | --> standby In other words where did you lose the WAL files? More to the point are they still on the master or have they already been recycled? Question: Do I need to sync all the data folder from my master Or just doing an incremental rsync would work? Thanks Patrick -- Adrian Klaver adrian.kla...@aklaver.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-sync slave server
Hi guys, I'm using PostgreSQL 9.2 There is a backup server that is 4 days old replication by wal_files (not streaming) However, the server went down and I lost some wal_files.. and now the replication is not working... Question: Do I need to sync all the data folder from my master Or just doing an incremental rsync would work? Thanks Patrick
Re: [GENERAL] Postgres Dropped DB have recovered files how to restore
lifetronics wrote: > This morning I accidently deleted my database for my OpenERP accounting. I > did not have a good backup system setup so I was unable to do a system > restore. i did manage to recover the files the drop command removed but I > dont know how to get the DB back into postgres? Can anyone help me with > this. I realy need this db to be restored otherwise I am screwed. I have no idea about Windows filesystems but you may be able to "undelete" the files, as long as you don't touch the partition for anything else; search the web for "undelete ntfs". Once you undelete you will need to put them back in the right places. I suggest you get a disk with twice as much space as the original; make an image copy of the original disk and don't touch the original anymore. Then try to undelete the files from the image. Use the list Adrian provided as a guide for what you're missing. You *may* be able to recover the data, if you're lucky and careful. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- 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] Postgres Dropped DB have recovered files how to restore
Ok, 1) back up the file system now as it is. So you can start over when this doesn't work. 2) create an empty database again with no data, but all of the DDL. 3) figure out the names of the new database files, and copy your old ones in with the same name as the new ones 4) cross your fingers. I have no idea if this will work 5) pg_dump everything and reload it into a new db. Let me know how it goes. Dave Cramer da...@postgresintl.com www.postgresintl.com On 6 June 2016 at 16:57, Adrian Klaver wrote: > On 06/04/2016 01:20 PM, lifetronics wrote: > >> This morning I accidently deleted my database for my OpenERP accounting. I >> did not have a good backup system setup so I was unable to do a system >> restore. i did manage to recover the files the drop command removed but I >> dont know how to get the DB back into postgres? Can anyone help me with >> this. I realy need this db to be restored otherwise I am screwed. >> >> These are the folders I recovered(i think my db was in the \base\19456 >> folder): >> >> C:\Program Files (x86)\OpenERP >> 7.0-20141104-021038\PostgreSQL\data\base\19456\ * >> >> C:\Program Files (x86)\OpenERP >> 7.0-20141104-021038\PostgreSQL\data\base\19456\ * >> >> C:\Program Files (x86)\OpenERP >> 7.0-20141104-021038\PostgreSQL\data\base\311296 >> >> C:\Program Files (x86)\OpenERP >> 7.0-20141104-021038\PostgreSQL\data\global\pg_internal.init\ * >> >> C:\Program Files (x86)\OpenERP >> 7.0-20141104-021038\PostgreSQL\data\pg_stat_tmp\ * >> >> I am running Windows Server 2012 R2 and postgres 9.3. Any help wil be much >> apreciated I am kind of desperate. >> > > This is what a complete $DATADIR for 9.3 looks like on a Linux source > install: > > postgres@killi:/usr/local/pgsql93/data> l > total 140 > drwx-- 16 postgres users 4096 Jan 11 09:06 ./ > drwxr-xr-x 7 root root 4096 Jun 27 2013 ../ > drwx-- 14 postgres users 4096 Jun 4 2015 base/ > drwx-- 2 postgres users 4096 Nov 18 2015 global/ > drwx-- 2 postgres users 4096 Jun 27 2013 pg_clog/ > -rw--- 1 postgres users 4476 Jun 27 2013 pg_hba.conf > -rw--- 1 postgres users 1636 Jun 27 2013 pg_ident.conf > drwxr-xr-x 3 postgres users 36864 Jan 11 08:42 pg_log/ > drwx-- 4 postgres users 4096 Jun 27 2013 pg_multixact/ > drwx-- 2 postgres users 4096 Jan 11 08:42 pg_notify/ > drwx-- 2 postgres users 4096 Jun 27 2013 pg_serial/ > drwx-- 2 postgres users 4096 Jun 27 2013 pg_snapshots/ > drwx-- 2 postgres users 4096 Jan 11 09:06 pg_stat/ > drwx-- 2 postgres users 4096 Jan 11 09:06 pg_stat_tmp/ > drwx-- 2 postgres users 4096 Apr 26 2015 pg_subtrans/ > drwx-- 2 postgres users 4096 Feb 23 2015 pg_tblspc/ > drwx-- 2 postgres users 4096 Jun 27 2013 pg_twophase/ > -rw--- 1 postgres users 4 Jun 27 2013 PG_VERSION > drwx-- 3 postgres users 4096 Jun 4 2015 pg_xlog/ > -rw--- 1 postgres users 20329 Jan 11 09:06 postgresql.conf > -rw--- 1 postgres users63 Jan 11 08:42 postmaster.opts > > > So if what you show is everything then you are short some important > directories. > > >> >> >> >> -- >> View this message in context: >> http://postgresql.nabble.com/Postgres-Dropped-DB-have-recovered-files-how-to-restore-tp5906468.html >> Sent from the PostgreSQL - general mailing list archive at Nabble.com. >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.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] Postgres Dropped DB have recovered files how to restore
On 06/04/2016 01:20 PM, lifetronics wrote: This morning I accidently deleted my database for my OpenERP accounting. I did not have a good backup system setup so I was unable to do a system restore. i did manage to recover the files the drop command removed but I dont know how to get the DB back into postgres? Can anyone help me with this. I realy need this db to be restored otherwise I am screwed. These are the folders I recovered(i think my db was in the \base\19456 folder): C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\base\19456\ * C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\base\19456\ * C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\base\311296 C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\global\pg_internal.init\ * C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\pg_stat_tmp\ * I am running Windows Server 2012 R2 and postgres 9.3. Any help wil be much apreciated I am kind of desperate. This is what a complete $DATADIR for 9.3 looks like on a Linux source install: postgres@killi:/usr/local/pgsql93/data> l total 140 drwx-- 16 postgres users 4096 Jan 11 09:06 ./ drwxr-xr-x 7 root root 4096 Jun 27 2013 ../ drwx-- 14 postgres users 4096 Jun 4 2015 base/ drwx-- 2 postgres users 4096 Nov 18 2015 global/ drwx-- 2 postgres users 4096 Jun 27 2013 pg_clog/ -rw--- 1 postgres users 4476 Jun 27 2013 pg_hba.conf -rw--- 1 postgres users 1636 Jun 27 2013 pg_ident.conf drwxr-xr-x 3 postgres users 36864 Jan 11 08:42 pg_log/ drwx-- 4 postgres users 4096 Jun 27 2013 pg_multixact/ drwx-- 2 postgres users 4096 Jan 11 08:42 pg_notify/ drwx-- 2 postgres users 4096 Jun 27 2013 pg_serial/ drwx-- 2 postgres users 4096 Jun 27 2013 pg_snapshots/ drwx-- 2 postgres users 4096 Jan 11 09:06 pg_stat/ drwx-- 2 postgres users 4096 Jan 11 09:06 pg_stat_tmp/ drwx-- 2 postgres users 4096 Apr 26 2015 pg_subtrans/ drwx-- 2 postgres users 4096 Feb 23 2015 pg_tblspc/ drwx-- 2 postgres users 4096 Jun 27 2013 pg_twophase/ -rw--- 1 postgres users 4 Jun 27 2013 PG_VERSION drwx-- 3 postgres users 4096 Jun 4 2015 pg_xlog/ -rw--- 1 postgres users 20329 Jan 11 09:06 postgresql.conf -rw--- 1 postgres users63 Jan 11 08:42 postmaster.opts So if what you show is everything then you are short some important directories. -- View this message in context: http://postgresql.nabble.com/Postgres-Dropped-DB-have-recovered-files-how-to-restore-tp5906468.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@aklaver.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] Why threads every 30 seconds?
Bryan Henderson writes: > Looking at audit logs, I see that my Postgresql server generates a new thread > precisely every 30 seconds, in two series (so 4 threads every minute). This > is an otherwise idle server. > Does anyone know what these threads are for? Just curious. Autovacuum, likely. The rate would depend on your autovacuum_naptime and how many active databases you have. 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] mobile apps: async repl/sync
Hallo, some loose thoughts on mobile app development. * Users want mobile apps to be quick and snappy as they call it. So if the app has to do (a lot of) queries to a remote datasource, it won't be fast and snappy. * For a while now json is a popular choice with programmers (of mobile apps, who will use for example json content with javascript), but i'm not convinced it will stay this popular in the future to come; it all depends on how well the programmer understands data. The reason: quick simply, json is an hierarchical structure with all the shortcomings that any hierarchical structure has as a datastore. (cf XML in the 90ties) Anyway the json datatype is there, a nice addition, giving these options to the developer. The relational model is likely to continue, mixed and extended with other forms of organising your data (as it many RDBMS already are). FDWs are the prime example of this. What if you want to store your data in a relational model for a mobile app that's snappy and fast? * U can't run postgres on the mobile device * U can use some lightweight relational database like sqlite. So logically if you want to combine them, having a remote database (eg postgres) and a local database(eg sqlite) -similar to the way json/mongodb or couch is used now- would be good for the future. So an easy async (partial) replication/synchronisation between for example sqlite and postgres would be very interesting for people who want to (quickly?) develop snappy mobile apps (often in need of a local and remote datastore) that benefit from the relational model. Many app developers don't really care about the data, they just want to make an app, so they ofen look at frameworks or stacks. Remember LAMP, i think this was a big reason for the succes of mysql. Now the same is happening with mongo, eg https://meanjs.org/ and others. https://thenapstack.wordpress.com/ is nice, but doesn't really offer a documented solution for those developers. I probably missed some techs out there; but this all being said, i would like to thank the community for their work. -- mvg, Wim Bertels -- ROMEO: Courage, man; the hurt cannot be much. MERCUTIO: No, 'tis not so deep as a well, nor so wide as a church-door; but 'tis enough, 'twill serve. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why threads every 30 seconds?
Looking at audit logs, I see that my Postgresql server generates a new thread precisely every 30 seconds, in two series (so 4 threads every minute). This is an otherwise idle server. Does anyone know what these threads are for? Just curious. -- Bryan Henderson San Jose, California -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres Dropped DB have recovered files how to restore
This morning I accidently deleted my database for my OpenERP accounting. I did not have a good backup system setup so I was unable to do a system restore. i did manage to recover the files the drop command removed but I dont know how to get the DB back into postgres? Can anyone help me with this. I realy need this db to be restored otherwise I am screwed. These are the folders I recovered(i think my db was in the \base\19456 folder): C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\base\19456\ * C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\base\19456\ * C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\base\311296 C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\global\pg_internal.init\ * C:\Program Files (x86)\OpenERP 7.0-20141104-021038\PostgreSQL\data\pg_stat_tmp\ * I am running Windows Server 2012 R2 and postgres 9.3. Any help wil be much apreciated I am kind of desperate. -- View this message in context: http://postgresql.nabble.com/Postgres-Dropped-DB-have-recovered-files-how-to-restore-tp5906468.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] postgres_fdw and Kerberos authentication
Jean-Marc, * Jean-Marc Lessard (jean-marc.less...@ultra-ft.com) wrote: > Stephen Frost [sfr...@snowman.net] wrote: > > The database owner operating system user has to be trusted, along with any > > superusers in the database, but if you assume those, then having PG manage > > the different Kerberos cache files > > (one for each backend which has authenticated via Kerberos and passed > > through delegation credentials) should work. > > Clearly, we can't give the user control over which credential cache to use. > > True, in such a case (single sign on) the user should not specify a user in > the user mapping, so that its own Kerberos ticket be used to authenticate. I don't know that it's actually an issue for the user to specify the mapping- either it'll be allowed or not, based on the credentials in the Kerberos cache and pg_ident mappings. What we can't do is allow the user to control which cache they are able to use. In other words, there should be one credential cache per backend process and that holds exactly the credentials which are forwarded from the client system. > > Having to trust the OS user and superusers with those credentials isn't any > > different from using passwords with postgres_fdw. > > OS user and superusers, should not have access and allowed to manage the > credential files. This isn't possible with traditional Unix permissions. Perhaps something could be done with SELinux, but we're not going to depend on that. Ultimately, the credential cache must be available to the backend process, which runs as the OS user. The PG superuser can execute arbitrary commands as the OS user, so there isn't any distinction between the OS user and the PG superuser. As mentioned up-thread, this is exactly the same as Apache, except that Apache happens to run as root whereas we run as a non-root user. > For example, in a secure environment with separation of duties at the > organization level (tier1, tier3, superuser, sys admins, etc), the tier1 DB > users cannot connect onto the DB server (as OS user), but may move data form > one database to another. Sure, I assumed that we were discussing a case where DB users connect to the database, not log on to the DB server as an OS user. > I agree that tier1 users cannot query the catalog and see other user > password, but a superuser can, which is considered a security breach by > auditors. > Storing a password in plain text even for a short period of time is > unfortunately not authorized. Agreed. This isn't the same as a Kerberos credential cache, but it's not as far different as one might assume either. The superuser will be able to access the credential cache of anyone who has forwarded their Kerberos ticket to the server, which is the same for any environment that allows Kerberos credential proxying. Thanks! Stephen signature.asc Description: Digital signature
Re: [GENERAL] Whither recovery.conf?
On Mon, Jun 6, 2016 at 10:12 PM, Vik Fearing wrote: > On 06/06/16 15:07, Vik Fearing wrote: >> It seems the commitfest link in there doesn't work anymore. I should >> probably bring that up in a separate thread. > > It's in the old commitfest app. Here's a new link for it: > https://commitfest-old.postgresql.org/action/patch_view?id=1293 Thanks! Richard. -- 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] Whither recovery.conf?
On 06/06/16 15:07, Vik Fearing wrote: > It seems the commitfest link in there doesn't work anymore. I should > probably bring that up in a separate thread. It's in the old commitfest app. Here's a new link for it: https://commitfest-old.postgresql.org/action/patch_view?id=1293 -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Whither recovery.conf?
On 06/06/16 14:50, Richard Tisch wrote: > Hi there, > > I was just wondering about the statement below in another thread: > > 2016-06-04 22:58 GMT+09:00 Vik Fearing : >> There are plans to allow SQL >> access to the parameters in recovery.conf (or to merge them into >> postgresql.conf) but that's not currently possible. > > Are there any active plans or patches on this front? I did a bit of searching, > I see one thread from 2013 (this one: > https://www.postgresql.org/message-id/CAJKUy5id1eyweK0W4+yyCM6+-qYs9erLidUmb=1a-qybgtw...@mail.gmail.com), > but it's not clear to where things are at the > moment. Mind you I might be searching in the wrong places and/or for the > wrong terms, it's happened before so apologies if I'm missing the obvious. The last I saw of it was in 2014 (wow, time flies). https://www.postgresql.org/message-id/flat/546D2316.2000206%40agliodbs.com It seems the commitfest link in there doesn't work anymore. I should probably bring that up in a separate thread. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] WAL's listing in pg_xlog by some sql query
* Vik Fearing (v...@2ndquadrant.fr) wrote: > On 03/06/16 04:32, Michael Paquier wrote: > > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar > > wrote: > >> On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost wrote: > >>> Given the usefulness of this specific query and that it could be used > >>> without risk of the user being able to gain superuser access through it, > >>> I'd like to see a new function added which does not have the superuser > >>> check, but is not allowed to be called by public initially either. > > CREATE FUNCTION ls_dir(text) > RETURNS SETOF text > LANGUAGE sql > SECURITY DEFINER > AS 'select * from pg_ls_dir($1)'; This isn't a good idea as it allows access to a great deal more than just the number of xlogs. Further, as described above, it gives that access to everyone and not just to specific roles. This is a great example of why we should provide an explicit function which is documented (both in our documentation and in the documentation of tools like check_postgres.pl) that users can use and can GRANT access to for their monitoring systems which gives access to only the information needed- that is, the number of xlog segments. Thanks! Stephen signature.asc Description: Digital signature
[GENERAL] Whither recovery.conf?
Hi there, I was just wondering about the statement below in another thread: 2016-06-04 22:58 GMT+09:00 Vik Fearing : > There are plans to allow SQL > access to the parameters in recovery.conf (or to merge them into > postgresql.conf) but that's not currently possible. Are there any active plans or patches on this front? I did a bit of searching, I see one thread from 2013 (this one: https://www.postgresql.org/message-id/CAJKUy5id1eyweK0W4+yyCM6+-qYs9erLidUmb=1a-qybgtw...@mail.gmail.com), but it's not clear to where things are at the moment. Mind you I might be searching in the wrong places and/or for the wrong terms, it's happened before so apologies if I'm missing the obvious. Thanks! Richard. -- Those who do not understand SQL are condemned to reinvent it, poorly -- 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] [HACKERS] OUT parameter and RETURN table/setof
On Mon, Jun 6, 2016 at 7:17 AM, Sridhar N Bamandlapally < sridhar@gmail.com> wrote: > Hi > > Is there any option in PGPLSQL which can RETURNS table or SETOF rows along > with an OUT parameter? > > No, there would be no point given the internals of how functions work. What is it you are trying to do? David J.
[GENERAL] OUT parameter and RETURN table/setof
Hi Is there any option in PGPLSQL which can RETURNS table or SETOF rows along with an OUT parameter? please Thanks Sridhar OpenText
Re: [GENERAL] ALTER TABLE and vacuum
On 06/06/16 10:55, Rick Widmer wrote: > Do I need to vacuum after an alter table command? If the specific command you do rewrites the table, you absolutely should vacuum in order to restore the visibility map. Otherwise, it not necessary. > Does it matter if there is a default or if we accept NULLs for the new > field? Yes, that makes a difference. If you add a column that defaults to NULL, the table will not be rewritten. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ALTER TABLE and vacuum
Do I need to vacuum after an alter table command? Does it matter if there is a default or if we accept NULLs for the new field? -- 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] Replication
Hi, Thx you for answering. Regards, Bertrand 2016-06-06 10:22 GMT+02:00 Vik Fearing : > On 06/06/16 09:54, Masahiko Sawada wrote: > > On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing wrote: > >> On 02/06/16 15:32, Bertrand Paquet wrote: > >>> Hi, > >>> > >>> On an hot standby streaming server, is there any way to know, in SQL, > to > >>> know the ip of current master ? > >> > >> No. > >> > >>> The solution I have is to read the recovery.conf file to find > >>> primary_conninfo, > >> > >> That is currently the only solution. There are plans to allow SQL > >> access to the parameters in recovery.conf (or to merge them into > >> postgresql.conf) but that's not currently possible. > > > > It might not be a right way but how about using pg_read_file()? > > postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'), > > '.*primary_conninfo = (.*)', '\1'); > > regexp_replace > > --- > > 'host=localhost port=5550 application_name=node1'+ > > > > (1 row) > > > > You can get the master server information via SQL from standby server. > > This is a good idea, but suffers the same problem that Bertrand has with > looking at the file a different way: if the file was changed but the > standby server has not been restarted, it's (potentially) not going to > be the correct information. > -- > Vik Fearing +33 6 46 75 15 36 > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support >
Re: [GENERAL] Replication
On 06/06/16 09:54, Masahiko Sawada wrote: > On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing wrote: >> On 02/06/16 15:32, Bertrand Paquet wrote: >>> Hi, >>> >>> On an hot standby streaming server, is there any way to know, in SQL, to >>> know the ip of current master ? >> >> No. >> >>> The solution I have is to read the recovery.conf file to find >>> primary_conninfo, >> >> That is currently the only solution. There are plans to allow SQL >> access to the parameters in recovery.conf (or to merge them into >> postgresql.conf) but that's not currently possible. > > It might not be a right way but how about using pg_read_file()? > postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'), > '.*primary_conninfo = (.*)', '\1'); > regexp_replace > --- > 'host=localhost port=5550 application_name=node1'+ > > (1 row) > > You can get the master server information via SQL from standby server. This is a good idea, but suffers the same problem that Bertrand has with looking at the file a different way: if the file was changed but the standby server has not been restarted, it's (potentially) not going to be the correct information. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Replication
On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing wrote: > On 02/06/16 15:32, Bertrand Paquet wrote: >> Hi, >> >> On an hot standby streaming server, is there any way to know, in SQL, to >> know the ip of current master ? > > No. > >> The solution I have is to read the recovery.conf file to find >> primary_conninfo, > > That is currently the only solution. There are plans to allow SQL > access to the parameters in recovery.conf (or to merge them into > postgresql.conf) but that's not currently possible. > It might not be a right way but how about using pg_read_file()? postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'), '.*primary_conninfo = (.*)', '\1'); regexp_replace --- 'host=localhost port=5550 application_name=node1'+ (1 row) You can get the master server information via SQL from standby server. Regards, -- Masahiko Sawada -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general