Re: [GENERAL] Optimizing a read-only database
Hello, In addition to what has already been suggested 1. Use VACUUM FREEZE ANALYZE; Otherwise you will still have some trickle of write-activity going on, not always efficiently, despite being in read-only mode. It's because of what's referred to as Hint Bits: http://wiki.postgresql.org/wiki/Hint_Bits 2. Low value for maintenance_work_mem Ref:http://postgresql.nabble.com/How-to-configure-a-read-only-database-server-td4311924.html regards Sameer -- View this message in context: http://postgresql.nabble.com/Optimizing-a-read-only-database-tp5849746p5850109.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] Optimizing a read-only database
Hello >That is bad advice. >If there are no writes, fsync won't hurt anyway. >Never disable fsync for anything but test systems. Yep. Its a bad way to speed up writes. Not relevant to this context and bad anyway regards Sameer -- View this message in context: http://postgresql.nabble.com/Optimizing-a-read-only-database-tp5849746p5850108.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] Optimizing a read-only database
Hello, >I was more dreaming of something like "disable read write locks or >mutexes" when accessing the database in read-only mode, but sadly this >case seems unhandled. You could use transactions in read only mode. They do not generate XID's,which reduces the need to do VACUUM to protect against XID wraparound. Ref: http://postgresql.nabble.com/read-only-transactions-td3209290.html -- View this message in context: http://postgresql.nabble.com/Optimizing-a-read-only-database-tp5849746p5850107.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] Optimizing a read-only database
Hello, You could disable fsync as write reliability is not relevant regards Sameer -- View this message in context: http://postgresql.nabble.com/Optimizing-a-read-only-database-tp5849746p5850103.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] pg_xlog Concern
Hello, >First: When I have given checkpoint segments=200 (As per my knowledge WAL file should be generated >200*2+3=403 only) but why it’s generating too much file. MY each WAL file is 16 MB. The formula is (2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1 files So what is your checkpoint_completion_target and wal_keep_segments? regards Sameer -- View this message in context: http://postgresql.nabble.com/pg-xlog-Concern-tp5849713p5850100.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] Merge requirements between offline clients and central database
Hello, As part of database evaluation one key requirements is as follows: 1. There are multiple thick clients (say 20 ~ 100) with their local databases accepting updates 2. They sync data with a central database which can also receive updates itself. 3. They may not be connected to central database all the time. 4. The central database receives and merges client data, but does not push data back to clients i.e. data between clients is not synced via central database or any other way. Is there anyway PostgreSQL, with any open source tool, can support such a scenario? How close can it be met? Any suggestions perhaps on a building a tool outside core which could bridge any gaps would help too Thank you Sameer -- 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] Using fmgr_hook
Hello, > Try like this: > - connect with psql > - call your custom_fmgr_hook > - attach to the backend with gdb > - set the breakpoint > - call custom_fmgr_hook again I tried to check in a different way, by just using log statements to see if customs* hook functions ever get executed. They were not. So it was not a breakpoint or debugger issue. Then instead of using auto_explain, we created a user defined sysdate function, implemented in C and added the fmgr hook code in there. Now we see custom hook functions being called. So looks like it was not a debugger issue, but fmgr hook work within user defined functions Thank you, Sameer -- 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] Using fmgr_hook
Hello, Thank you for responding >My experience is that you cannot set breakpoints before the library >is loaded, so you first have to call a function in the library, then >you interrupt and set the breakpoint. I tried to do the following 1. Execute Postgres (now auto_explain is loaded) 2. Start a psql session and attach gdb to forked Postmaster process 3. Now set break point in custom_fmgr_hook 4. Execute select * from now(); Still the breakpoint gets skipped. Also i checked by putting a breakpoint in explain_ExecutorStart before starting Postgres (before auto_explain is loaded), and then started psql session, attached gdb and executed select* from now(), in this case the debugger does stop at the breakpoint. The strange thing is i could swear that i had got the debugger to stop in custom_fmgr_hook , just after i added custom_needs_fmgr_hook and had figured out that we need to use custom_needs_fmgr_hook and custom_fmgr_hook together. But i have not been able to reproduce that. So maybe there is something in what you say. I just cannot nail the sequence correctly regards Sameer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using fmgr_hook
Hello, In the process of implementing my own version of sysdate, i was trying to use the fmgr_hook. I had a look at the sepgsql contrib module and tried to do the same by modifying auto_explain just to test using fmgr_hook. My code changes are: static needs_fmgr_hook_type prev_needs_fmgr_hook = NULL; static fmgr_hook_type prev_fmgr_hook = NULL; static bool custom_needs_fmgr_hook(Oid functionId); static void custom_fmgr_hook(FmgrHookEventType event,FmgrInfo *flinfo, Datum *private); in PG_init(void) prev_needs_fmgr_hook = needs_fmgr_hook; needs_fmgr_hook = custom_needs_fmgr_hook; prev_fmgr_hook = fmgr_hook; fmgr_hook = custom_fmgr_hook; in _PG_fini(void) needs_fmgr_hook=prev_needs_fmgr_hook; fmgr_hook=prev_fmgr_hook; static bool custom_needs_fmgr_hook(Oid functionId) { return true; } void custom_fmgr_hook(FmgrHookEventType event,FmgrInfo *flinfo, Datum *private) { if(flinfo->fn_extra == NULL) { TimestampTz current_timestamp = GetCurrentTimestamp(); flinfo->fn_extra = palloc(sizeof(TimestampTz)); flinfo->fn_extra = (void*) current_timestamp; } } To debug i have a breakpoint inside custom_fmgr_hook. Debugging: 1. Start postgres 2. Start psql connecting to postgres 3. Attach gdb to process spawned off by postmaster representing psql session. 4. execute select * from now(); Problem: The breakpoint seems to get skipped. Just to be sure i put a breakpoint in explain_ExecutorStart and i could debug that function. So i am attaching gdb to correct process. What am i doing wrong? Thank you, Sameer -- 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] Mimicking Oracle SYSDATE
Hello Tom, >Do you really need to be bug-compatible with Oracle's SYSDATE at that >level of detail? We did a Oracle to PostgreSQL migration recently, and migrating sysdate was an issue. Figuring out whether to use clock_timestamp, timestamp(0), and cases in which both options are incorrect, took time. So we are trying to build an automated solution which can figure out context of sysdate and work it the same in PostgreSQL. Its easier to show the client that stuff works exactly the same before and after migration, bug or no bug. >Anyway, the approach I'd think about using Thank you, this is what we are looking for! >On the whole, much the best advice would be to explicitly read >clock_timestamp() at the points where you'd like time to advance, >eg convert the above to. We understand. Appreciate the thoughts given, will keep you posted regards Sameer On Tue, Aug 19, 2014 at 8:21 PM, Tom Lane wrote: > Sameer Thakur writes: >> We are thinking of building our own version of Oracle's sysdate, in >> the form of PostgreSQL extension. >> Consider the behavior of sysdate for multiple inserts inside a function > >> CREATE OR REPLACE FUNCTION fun2 >>RETURN number >>IS var1 number(10); var2 number (2); >> BEGIN >> insert into t1 select sysdate from dual connect by rownum<=7; >> FOR var1 IN 0..100 >> LOOP >> SELECT 0 iNTO var2 FROM dual; >> END LOOP; >> insert into t1 select sysdate from dual connect by rownum<=7; >> RETURN var1; >> END; > >> The result of all first 7 rows are same and result of all second >> 7 row are same. But there is a difference between the values >> returned by sysdate between the 2 loops. > > Do you really need to be bug-compatible with Oracle's SYSDATE at that > level of detail? Especially seeing that SYSDATE is only precise to > 1 second to start with? In most situations you could not tell the > difference as to whether SYSDATE had advanced within a function or not; > and, very likely, if it did visibly advance that would actually be a bug > so far as behavior of the function was concerned, because it would be > a case that hadn't been considered or tested. I suspect whoever thinks > they have a requirement here hasn't actually thought very hard about it. > > If you think you do need bug-compatibility then the above is far from > a precise specification, eg it doesn't address what should happen in > sub-selects that are executed multiple times by the surrounding query. > > Anyway, the approach I'd think about using is to rely on the ability of > C functions to cache query-lifespan data in fn_extra, ie > (1) if fn_extra is NULL then read current timestamp and > store it at *fn_extra > (2) return *fn_extra > This would give you one reading per query execution per textual > occurrence of "sysdate()", which would satisfy the example above. > > A possible problem is that once in awhile, something like > SELECT sysdate(), sysdate() > would give two different answers. I'm not sure if that's possible > with SYSDATE. > > [ thinks for a bit... ] Actually this might not work so well for > sysdate() appearing in simple expressions in plpgsql. I think the > expression evaluation tree is cached for the whole transaction in > such cases. You'd have to test it. > > On the whole, much the best advice would be to explicitly read > clock_timestamp() at the points where you'd like time to advance, > eg convert the above to > >timestampvar := date_trunc('second', clock_timestamp()); >insert into t1 select timestampvar from ... > > 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] Mimicking Oracle SYSDATE
Hello, We are thinking of building our own version of Oracle's sysdate, in the form of PostgreSQL extension. Consider the behavior of sysdate for multiple inserts inside a function CREATE OR REPLACE FUNCTION fun2 RETURN number IS var1 number(10); var2 number (2); BEGIN insert into t1 select sysdate from dual connect by rownum<=7; FOR var1 IN 0..100 LOOP SELECT 0 iNTO var2 FROM dual; END LOOP; insert into t1 select sysdate from dual connect by rownum<=7; RETURN var1; END; The result of all first 7 rows are same and result of all second 7 row are same. But there is a difference between the values returned by sysdate between the 2 loops. PostgreSQL's statement_timestamp is not a good substitute for sysdate in this case as it returns same value for both loops. However if there are multiple inserts outside a function statement_timestamp(0) it seems to work the same as sysdate. Our implementation sysdate hence needs to figure out the context in which its called i.e. within a function or from outside. Also we have a similar need to figure out if its been called for multiple inserts (in a loop or simple insert statements one after another) as this affects behavior as well. So in short if we have a custom function how do we figure out 1) if its called from within another function 2) called within a loop 3) called multiple times outside a loop since this would alter what it returns Any idea how to implement this? regards Sameer -- 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] Stored Procedure table/column args
Hello, Create a view as described @ http://www.postgresql.org/message-id/flat/3c055b7e.bb52f...@but.auc.dk#3c055b7e.bb52f...@but.auc.dk create view my_tbldescription as select u.usename, t.typname AS tblname, a.attname, a.atttypid, n.typname AS atttypname, int4larger(a.attlen, a.atttypmod - 4) AS atttyplen, a.attnotnull, a.attnum from pg_user u, pg_type t, pg_attribute a, pg_type n where u.usesysid = t.typowner and t.typrelid = a.attrelid and t.typtype = 'c' and not (t.typname ~* 'pg_') and n.typelem = a.atttypid and substr(n.typname, 1, 1) = '_' and a.attnum > 0 ; And then create functions using that view. create or replace function table_exists (tbl varchar) returns boolean AS $$ DECLARE x integer; BEGIN Execute 'select count(*) from my_tbldescription where tblname=$1' into x using tbl; if (x>0) then RETURN TRUE; else RETURN FALSE; end if; END; $$ LANGUAGE plpgsql; create or replace function column_exists (col varchar) returns boolean AS $$ DECLARE x integer; BEGIN Execute 'select count(*) from my_tbldescription where attname=$1' into x using col; if (x>0) then RETURN TRUE; else RETURN FALSE; end if; END; Regards Sameer
Re: [GENERAL] Self referencing composite datatype
> > On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur wrote: > >> Hello, >> I wanted to create a composite datatype to represent a Node. So it would >> have a few attributes and an array of type Node which is the children of >> this node. >> create type Node as (r integer, s integer, children Node []); >> But i get error type Node[] does not exist. I understand that Node is not >> defined hence the error. >> But how do i get around this problem? >> > > What exactly are you trying to accomplish? I can think of a number of > ways. > > For example, suppose we have a table like: > > create table node ( >id int primary key, >parent int references node(id), >content text not null > ); > > We could create a function like this: > > CREATE FUNCTION children(node) RETURNS node[] LANGUAGE SQL AS > $$ > SELECT array_agg(node) FROM node WHERE parent=$1.id; > $$; > > Then we could still do: > > select n.children FROM node n WHERE id = 123; > > Note that causes two separate scans, but should work. > > Thank you. I am trying to capture plan statistics for every node in the plan tree. For this i have plan view which has plan specific information like planid,plan_text, and root_node of type Node. Type Node has node specific statistics like estimated_startup_cost, estimated_actual_cost etc. It also has child_nodes of type Node[]. i face the problem of self referencing composite type in defining Node type. The reason i wanted a type Node is because while initializing memory for my contrib module i have GUC parameter specifying max number of Nodes. I was thinking that it is possible to initialize memory with a sizeof(Node). Still trying to figure out how using a table storing Node will help me in figuring out how much initial memory can be allocated regards Sameer
[GENERAL] Self referencing composite datatype
Hello, I wanted to create a composite datatype to represent a Node. So it would have a few attributes and an array of type Node which is the children of this node. create type Node as (r integer, s integer, children Node []); But i get error type Node[] does not exist. I understand that Node is not defined hence the error. But how do i get around this problem? regards Sameer
Re: [GENERAL] Populating array of composite datatype
Thank you Raghavendra and Chris, both approaches work.
[GENERAL] Populating array of composite datatype
Hello, I have a composite datatype abc which has two integer fields x,y. I have a table Test which has an array of abc. I am trying to populate Test. Tried insert into test values (ARRAY[abc(1,2)]); but got error ERROR: function abc(integer, integer) does not exist Is there anyway for doing this? regards Sameer
Re: [GENERAL] Archiving and recovering pg_stat_tmp
> > >> >Do you think that it is important to do so? Are you experiencing > problems which you believe are due to missing/out of date >activity > statistics? Or is this more for curiosity? > I am making specifications for a tool which captures query plan > statistics. I wanted its behavior to be the same as statistics collector > for everything except in the kind of statistics being collected. This is > the same premise used by pg_stat_statements. Hence the question wrt PITR > recovery. > In the case of my tool there could be as case that user wants to see the information like 5 slowest queries after archive recovery. For this i would need to build in logic to ensure the stats file read in during recovery is in sync with that timeline. It sounds complicated so was wondering how statistics collector handled it. Seems to me that the stats file is ignored in archive recovery mode,and deleted. This is consistent with its crash recovery behavior as well. regards Sameer
Re: [GENERAL] Archiving and recovering pg_stat_tmp
> > > > I did the following > > 1. Enabled archiving > > 2. Executed SQL's 3. Shutdown 4. copied data directory (including the > > pgstats.stat under global) and archive directory under backup/1 > > repeated 2,3,4 once more > > So now i have 2 backup directories. > > Now i created recovery.conf under backup/1/data. It has recovery command > > copying from backup1 archive. > > Next i renamed pagstat.stat under backup1/data/global as pgstat_1.stat. > > Next i copied pgstat.stat from backup 2 into backup 1 (under the usual > > global directory). > > Pointed the server to backup1 data directory and started it. > Surprisingly it > > works with no complaints about wrong pgstat.stats > > > > >Do you have only one archive set up? What do you mean when you mention > >"backup1" archive? >What kind of complaints did you see when you did in past? I am not > >aware of such errors; am I missing some? > I am sorry for being unclear. directories are /backup1/data /backup1/archive and /backup2/data /backup2/archive backup1 has data and archive directories copied after 1st shutdown backup2 has data and archive directories copied after 2nd shutdown Now i created recovery.conf in backup1/data and i replaced pgstats.stat from backup2 in backup1 (in data/global) Now i start Server pointing to backup1, where it uses recovery.conf to rollback to database snapshot after first shutdown I expected a failure because pgstats.stat do not reflect statistics of snapshot created from backup1 directory (1st shutdown) but of backup2 (2nd shutdown) regards Sameer
Re: [GENERAL] Archiving and recovering pg_stat_tmp
>>But, if you do PITR using the same directory (which I haven't), I >>think you would need to somehow replace the stats with the ones you >>want, may be from your backup of the same (that is, of >>pg_stat/*.stat), though I am not sure if that would be correct. I >>doubt if WAL replay (as in a consistent recovery mechanism :-) ) >>accounts for the stats. I guess stats are not WAL logged (like changes >>to table data) since they are managed using temporary files in >>pg_stat_temp and hence may not be recoverable using WAL replay to a >>particular state using PITR. but I may be wrong. >>Thoughts? > I agree. Will try PITR with stats file from different timeline and confirm this I did the following 1. Enabled archiving 2. Executed SQL's 3. Shutdown 4. copied data directory (including the pgstats.stat under global) and archive directory under backup/1 repeated 2,3,4 once more So now i have 2 backup directories. Now i created recovery.conf under backup/1/data. It has recovery command copying from backup1 archive. Next i renamed pagstat.stat under backup1/data/global as pgstat_1.stat. Next i copied pgstat.stat from backup 2 into backup 1 (under the usual global directory). Pointed the server to backup1 data directory and started it. Surprisingly it works with no complaints about wrong pgstat.stats Thoughts? regards Sameer >> >> >
Re: [GENERAL] Archiving and recovering pg_stat_tmp
On Fri, Jun 21, 2013 at 11:35 AM, Amit Langote wrote: > On Fri, Jun 21, 2013 at 2:44 PM, Sameer Thakur > wrote: > > > >> >"You need to have statistics recovered to the same state as they were > >> >when you took the FS level backup of your database after shutting down > >> >the server." > > > > Correct > >> > >> > >> >"Shutting down" is important since that is when you would have > >> >statistics files ($PGDATA/pg_stat/*.stat) available to backup. They > >> >capture the statistics as of when the server was shut down. > >> Agreed > > > > > >>What I did: > >> > >> > >> >1) Collect a few statistics in a result file from a currently running > >> >server. For example, the result of the query "select * from > >> >pg_stat_user_tables", into say stats1.txt > >> > >> >2) Clean shut down the server. Take a snapshot of the data directory, > >> >"cp -r $pgdata $pgbkp" > >> > >> >3) Start the server and run a few pgbench tests so that statistics > >> >change. Again collect stats, same as in (1) into say stats2.txt > >> > >> >4) Write $pgbkp/recovery.conf with appropriate restore_command and > >> >maybe recovery target (PITR), which I did not, though. Note that we > >> >have archiving enabled. > >> > >> >5) Start the server using -D $pgbkp (may be with port changed for the > >> >sake of testing). > >> > >> >6) After server started in (5) is done recovering and comes online, > >> >collect stats again into say stats3.txt > >> > >> >7) Compare stats3.txt with stats1.txt and stats2.txt. > >> > >> >8) I observed that stats3.txt == stats1.txt. That is stats after > >> >recovery are same as they were when the snapshot was taken. > >> > >> Thank you for all the effort! A question > > > > When server was restarted in (5) which stats file was loaded > stats1.txt or > > stats.2.txt?. I think it must have been stats1.txt as stats3.txt = > > stats1.txt. What happens if stats2.txt is loaded on (5) instead on > > stats1.txt? I am trying to figure out if the Server will reject stats > file > > from a different timeline than the one its been rolled back to. > > >I started the server in step (5) using the back up directory. And > >remember backup directory would contain stats as they are in > >stats1.txt. So, there wasn't a possibility of stats as they are in > >stats2.txt to be loaded. > Understood > >But, if you do PITR using the same directory (which I haven't), I > >think you would need to somehow replace the stats with the ones you > >want, may be from your backup of the same (that is, of > >pg_stat/*.stat), though I am not sure if that would be correct. I > >doubt if WAL replay (as in a consistent recovery mechanism :-) ) > >accounts for the stats. I guess stats are not WAL logged (like changes > >to table data) since they are managed using temporary files in > >pg_stat_temp and hence may not be recoverable using WAL replay to a > >particular state using PITR. but I may be wrong. > > >Thoughts? > I agree. Will try PITR with stats file from different timeline and confirm > this regards Sameer > > -- > Amit Langote >
Re: [GENERAL] Archiving and recovering pg_stat_tmp
> >"You need to have statistics recovered to the same state as they were > >when you took the FS level backup of your database after shutting down > >the server." > Correct > > >"Shutting down" is important since that is when you would have > >statistics files ($PGDATA/pg_stat/*.stat) available to backup. They > >capture the statistics as of when the server was shut down. > Agreed >What I did: > > >1) Collect a few statistics in a result file from a currently running > >server. For example, the result of the query "select * from > >pg_stat_user_tables", into say stats1.txt > > >2) Clean shut down the server. Take a snapshot of the data directory, > >"cp -r $pgdata $pgbkp" > > >3) Start the server and run a few pgbench tests so that statistics > >change. Again collect stats, same as in (1) into say stats2.txt > > >4) Write $pgbkp/recovery.conf with appropriate restore_command and > >maybe recovery target (PITR), which I did not, though. Note that we > >have archiving enabled. > > >5) Start the server using -D $pgbkp (may be with port changed for the > >sake of testing). > > >6) After server started in (5) is done recovering and comes online, > >collect stats again into say stats3.txt > > >7) Compare stats3.txt with stats1.txt and stats2.txt. > > >8) I observed that stats3.txt == stats1.txt. That is stats after > >recovery are same as they were when the snapshot was taken. > > Thank you for all the effort! A question > When server was restarted in (5) which stats file was loaded stats1.txt or stats.2.txt?. I think it must have been stats1.txt as stats3.txt = stats1.txt. What happens if stats2.txt is loaded on (5) instead on stats1.txt? I am trying to figure out if the Server will reject stats file from a different timeline than the one its been rolled back to. regards Sameer > >
Re: [GENERAL] Archiving and recovering pg_stat_tmp
>Documentation mentions following: Thanks, but how does this relate to statistics recovery wrt PITR? regards Sameer
[GENERAL] Archiving and recovering pg_stat_tmp
Hello, I was trying to figure out how does one recover server statistics to the same snapshot to which a database is restored after PITR. The steps i had in mind were 1.Set up WAL archiving 2.On server shutdown one would need to backup pg_stat_tmp along with file system level back of database 3. On server crash setup configuration for recovery mode 4. Restart server, which replays WAL files and hen moves from recovery to normal mode What will be behavior be regarding pg_stat_tmp? Will it be deleted on startup? Is it possible to recover the same statistics as on last server shutdown? ICan the statistics recovered to the same PITR? Thank you Sameer