Re: [GENERAL] table design and data type choice

[Sameer Kumar]

On Wed, Jan 8, 2014 at 3:11 PM, Jayadevan M wrote:

> We have a table to record the voteup/votedown by users of questions and
> answers (like on stackoverflow). So there will be a large number of inserts
> (voteup/down), some updates(user changes mind)and may be a few deletes. The
> queries will be mostly aggregates (count(*) where question_id=  and
> vote_up =1) . Is it better to have data type of Boolean, varchar or int?
>
> I assume there isanother decision too - have 2 columns - one for up and
> one for down, or have just one column which will be 1 or -1.
>

This is more of a design call. I would go with approach of having one
column.
But when you talk about voting a vote could either be up or down? Why will
you need two columns? Can up and down be false at same time? Or both be
true at same time? If they are mutual exclusive you should consider having
one column.

If the queries are always going to be based on vote='up' or vote=1 or
vote=t (?), then you can use partial indexes to optimize the access and
updates etc.

http://www.postgresql.org/docs/9.3/static/indexes-partial.html



Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350 <%2B65%208110%200350>* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).
<>

Re: [GENERAL] Sudden slow down and spike in system CPU causes max_connections to get exhausted

[Sameer Kumar]

On Tue, Jan 7, 2014 at 9:06 AM, Anand Kumar, Karthik <
karthik.anandku...@classmates.com> wrote:

> We do typically have a lot of idle connections (1500 connections total,
> over a 1000 idle at any given time). We're in the midst of installing
> pgbouncer to try and mitigate the problem, but that still doesn't address
> the root cause.
>

1500 connections in total (is that the number you are using for your
max_connections too)? How many CPUs/CPU threads you have on your server?
How many concurrent transactions are happening in your database? 1000 idle
at any give time? Whoa! Is that by design or a defect which you have
decided to live with? 1000 idle connection are idle or idle in transaction?


What pooling mode are you using in pgbouncer? Going by your description, I
will suggest that you use transaction mode. Since you either have bugs or
deliberately (why? why?) keep connections open/idle.

So in sessions mode you may not get much benefits from pgbouncer.

While you use pgbouncer you can set max_clients to a higher number and try
to set timeouts for your clients (note that it will timeout even those
clients who are in middle of transaction but are idle for long).


Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD. *101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).
<>

Re: [GENERAL] How to know server status variable in postgresql?

[Sameer Kumar]

On Tue, Jan 7, 2014 at 8:33 PM, ambilalmca  wrote:

> Hai, I am developing a java application for performance counter. For that i
> want to collect all server status counter names with current value. i just
> did it for MySQl by, *"SHOW GLOBAL STATUS"*.
>

What details do you want to collect? That command in MySQL may be giving
you few counters, but which of those are of your interest?



Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).
<>

[GENERAL] returning json object with subset of keys

[Raphael Bauduin]

Hi

I'm using the json functionalities of postgresql 9.3.
I have a query calling json_populate_recordset like this:
  json_populate_recordset(null::product, event->'products')
but it returns an error:
ERROR:  cannot call json_populate_recordset on a nested object

There is indeed one key in event->'products' giving access to an array of
objects.

Is there a way to specify which keys to keep from the object? I haven't
found ti in the docs.

Here is pseudo code of what I'd like to do:
  json_populate_recordset(null::product, event->'products' WITH ONLY KEYS
{'f1','f2'})

Thx


-- 
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org event->'products'

Re: [GENERAL] returning json object with subset of keys

[Merlin Moncure]

On Wed, Jan 8, 2014 at 6:37 AM, Raphael Bauduin  wrote:
> Hi
>
> I'm using the json functionalities of postgresql 9.3.
> I have a query calling json_populate_recordset like this:
>   json_populate_recordset(null::product, event->'products')
> but it returns an error:
> ERROR:  cannot call json_populate_recordset on a nested object
>
> There is indeed one key in event->'products' giving access to an array of
> objects.
>
> Is there a way to specify which keys to keep from the object? I haven't
> found ti in the docs.
>
> Here is pseudo code of what I'd like to do:
>   json_populate_recordset(null::product, event->'products' WITH ONLY KEYS
> {'f1','f2'})

unfortunately, not without manipulating the json.  this is basically a
somewhat crippling limitation of the json_populate functions -- they
can't handle anything but flat tuples.  so you have to do something
highly circuitous.

problem (one record):
postgres=# create table foo(a text, b text);
postgres=# select json_populate_record(null::foo, '{"a": "abc", "b":
"def", "c": [1,2,3]}'::json);
ERROR:  cannot call json_populate_record on a nested object

nasty solution:
postgres=# with data as (select '{"a": "abc", "b": "def", "c":
[1,2,3]}'::json as j)
select json_populate_record(null::foo, row_to_json(q)) from
(
  select j->'a' as a, j->'b' as b from data
) q;
 json_populate_record
--
 (abc,def)

with some extra manipulations you can do a record set. basically, you
need to get the json 'right' first (or that can be done on the
client).

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] Consistent file-level backup of pg data directory

[gator_ml]

On 2014-01-07 21:54, Jeff Janes wrote:
> On Tue, Jan 7, 2014 at 10:58 AM,  > wrote:
> - keep a copy "backup_label" under a different name
[...[
> Why under a different name?  That sounds dangerous to me.
... otherwise it would be useless, because "pg_stop_backup"
will delete the "backup_label" file

> - call pg_stop_backup()
> - the run the regular backup procedure
>
>
> You have this order backwards.  You can only tell postgres that you are
> done doing the backup once you are done doing the backup.
this is what I tried 1st - unfortunately, this won't work at all,
because postgres creates another checkpoint _after_ pg_stop and will
refuse to run the restore procedure if this last wal file is missing
(which in my scenario inevitably will be the case)

> It is pretty much a disaster.  Do you really need rsync?  Otherwise, use
> pg_basebackup, it is easier to get right.

... it looks like I didn't explain clear enough, what I am trying to
accomplish. So let me try it again:

We have a general backup procedure, that runs every night. For
some machines, there may additionally be more
specialized/fine-grained measures, but in any case, it should be
possible to restore any given server without any machine-specific
knowledge from the last nightly backup following a
generic "restore-recipe". Our current rsync-based solution is a
pretty good compromise between many different requirements.

For machines running database systems, this means, this means,
that I need some way to get a consistent state of some point in
time. It does not particularly matter, which time exactly (in
particular, I do not care, if transactions committed during the
backup are included or not) For this purpose, I can define
machine-specific hook to run before/ after the actual backup
procedure.

Unfortunately, it does not look like there is any direct way to
accomplish this with postgres except shutting down the whole
database system while the backup is running. The systems will
be almost idle while the backup runs, and write transactions
will be very rare, but a total shutdown for every backup still
would be too radical.

Actually, with some tests copying the database directory while
writing data to the database and then restarting postgres with
such a copy, I did not manage to produce any visible
inconsistencies. If postgres was able to restore the last
consistent state in such a situation, then no additional measures
would be needed (like I said, it is not particularly important
which state this is - losing everything written during the last
15 minutes including committed transactions would be good enough)
.But at least as far as I can tell from the documentation, this
can not safely be assumed.

Otherwise, the procedures for "Continuous Archiving" are clearly
made for some a diffent purpose, it seems like this is about as
close as it gets to what I need. As far as I understand:

- between pg_start_backup() and pg_stop_backup() it should be safe
to copy the data directory any time
- all committed transactions during this time will go to the wal files
(actually, I noticed that files in other directories than pg_xlog are
modified, too ...)
- normally, it is intended to copy every single newly written wal file
somewhere else by whatever is defined as "archive_command", but
if this works, than any other way to copy all wal files written
after pg_start_backup should work just as well.

My original idea was, to call pg_start_backup() 1st, then run the
actual backup followed by pg_stop_backup. Because the procedure
for " Point-in-Time Recovery" is the same and no wal file may be
modified after it was written and "archive_command" called, it
follows, that any wal file plus the preceding wal files written
after pg_start_backup must contain everything that is needed to
restore any state up to the time the wal file was
written (otherwise the whole "PITR)" mechanism would not work).
Unfortunately, postgres refuses to run the restore procedure if
the last wal file (which is written after pg_stop_backup, so in
my case it will not be available) even if "recovery_target_time"
is explicitly set to some earlier time. I didn't try it, but
assuming that the last wal file is not really needed in this
case, it would be enough to just create an empty file with the
name postgres is looking for?

If I can't find, a better way, I probably could arrange to
separately copy this last wal file into the
backup (unfortunately, there is no easy way to do this ...)-:

Calling pg_stop_backup() _before_ the backup was of course sheer
desperation but this way postgres seemed to correctly run it's
restore procedure in my tests, but I don't know about the
postgres internals (actually, I personally am not even
responsible for any postgres server), that's why I asked here.

So again my question: is there any reasonable way to get a
consistent backup of a postgres server in my
scenario (pg_basebackup won't help much here - I could of course
use

[GENERAL] Last inserted row id with complex PK

[Nelson Green]

I have a projects log table with a three column PK, project_num,
person_num, and sequence, where each new entry for a project/person
combination increments the sequence, which is not an auto incrementing
sequence. Is there any way to retrieve the last entry to the table? For
instance, if the last entry for person 427 on project 15 was sequence
number 125, is that information available to me anywhere?

Thanks,
Nelson

Re: [GENERAL] Last inserted row id with complex PK

[Francisco Olarte]

Hi Nelson:

On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green  wrote:
> I have a projects log table with a three column PK, project_num, person_num,
> and sequence, where each new entry for a project/person combination
> increments the sequence, which is not an auto incrementing sequence. Is
> there any way to retrieve the last entry to the table? For instance, if the
> last entry for person 427 on project 15 was sequence number 125, is that
> information available to me anywhere?

SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
project_num=15

If it's a PK it should have an index and I believe it will be quite fast.

AAMOF, you could even build the above with max+1 into the insert query
for a new entry and have the values returned using a RETURNING clause.

Francisco Olarte.


-- 
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] Last inserted row id with complex PK

[Tom Lane]

Francisco Olarte  writes:
> Hi Nelson:
> On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green  wrote:
>> I have a projects log table with a three column PK, project_num, person_num,
>> and sequence, where each new entry for a project/person combination
>> increments the sequence, which is not an auto incrementing sequence. Is
>> there any way to retrieve the last entry to the table? For instance, if the
>> last entry for person 427 on project 15 was sequence number 125, is that
>> information available to me anywhere?

> SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
> project_num=15

Note that this will not work terribly well if there are concurrent
insertions for the same person/project.  If that's not an issue, though,
it should be fine.  It may be OK even if there are sometimes concurrent
insertions, if you are prepared to retry duplicate-key failures.

> If it's a PK it should have an index and I believe it will be quite fast.

It will be fast as long as sequence is the low-order column in the index.

regards, tom lane


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

Re: [GENERAL] How to know server status variable in postgresql?

[ambilalmca]

I want to collect,

*Connections. *

Current connections. The number of currently open connections.
Connections executing requests. The number of currently open connections
that are executing requests.
Idle connections. The number of currently idle connections.
Max connections. The maximum number of concurrent connections to the
database server.
Used connections. Connections used as a percentage of maximum connections.

*Buffers. *

Shared buffers size. Current size of shared buffers.
Disk cache size. Current size of disk cache buffer.
Sort buffer size. Current size of sort buffer.
Work buffer size. Current size of working requests buffer.
Temp buffer size. Current size of temporary buffer.

*IO Requests.* 

Blocks read. Number of blocks directly read on disk.
For optimal performance this value should be the smallest possible. If the
database has to execute too many disk accesses, performance will suffer.
Index blocks read. Number of index blocks directly read on disk.
% Index blocks read. Percentage of index blocks directly read on disk.
Sequence blocks read. Number of sequence blocks directly read on disk.

*Cache*
Blocks read. Number of cached blocks read.
Index blocks read. Number of cached index blocks read.
% Index blocks read. Percentage of cached index blocks read.
For optimal performance, this value must be as large as possible. If an
insufficient number of index blocks are declared in the table, it could
negatively impact the database server performance.
Sequence blocks read. Number of cached sequence blocks read.

*Index*.
 
Index read. Number of reads initiated by an index.
Indexed rows read. Number of rows read by indexed requests.
Indexed rows fetched. Number of live rows fetched by indexed requests.

*Command Rates.* 

Rows read. Number of rows read.
Rows fetched. Number of rows fetched.
Inserted rows. Number of rows inserted.
Updated rows. Number of rows updated.
Deleted rows. Number of rows deleted.
Committed transactions. Number of committed transactions.
This value should be relatively stable, indicating that there are no
performance-reducing load peaks. If applications do not commit often enough,
it will lead to an overload on the database server.
Rolled back transactions. Number of transactions rolled back.
% Rolledback transactions. Percentage of transactions rolled back.

*Locks. *

Locks waiting. Number of locks waiting.
Locks held. Number of locks held.
Process holding locks. Number of processes holding locks.

how to collect these details by using query. now i find queries for
sonnections. but i dont know anout others. please help me.@Sameer Kumar 



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5785833.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] Planning error in dynamic string creation in plpgsql

[Keith Fiske]

Just wanted to say thanks again for the help to those that responded. For
anyone curious, this helped me get a more advanced constraint exclusion
feature finished for the partition manager I've been working on

http://www.keithf4.com/managing-constraint-exclusion-in-table-partitioning/

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com


On Mon, Jan 6, 2014 at 11:33 AM, Keith Fiske  wrote:

> David,
>
> That seems to have fixed it! I was going down a path of grabbing the
> column's type from pg_attribute and trying to work from there, but was
> still having some of the same issues.
>
> Thanks everyone else that replied as well!
>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com
>
>
> On Mon, Jan 6, 2014 at 10:42 AM, David Johnston  wrote:
>
>> Adrian Klaver-3 wrote
>> >>
>> >>
>> >> In the real function I'm writing, the columns to be used in the string
>> >> being created are pulled from a configuration table, so their types
>> >> could be anything. So casting the quote_literal() calls is not really
>> an
>> >> option here.
>> >>
>> >> Any help would be appreciated.
>> >
>> > Cast before the quote_literal?
>> >
>> > Example:
>> >
>> > EXECUTE 'SELECT min('||v_col||'::text) as min, max('||v_col||'::text) as
>> > max FROM test_temp' INTO v_record;
>>
>> Right idea if the loss of original type in the min/max query is acceptable
>> (which it should be).  But the cast wants to be of the min/max RESULT not
>> the min/max INPUT.
>>
>> SELECT min(' || v_col || ')::text AS min_text, max(' || v_col || ')::text
>> AS
>> max_text FROM ...
>>
>> Min/Max logic wants to be done by the rules for the original type, not
>> according to string collation rules.
>>
>> David J.
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://postgresql.1045698.n5.nabble.com/Planning-error-in-dynamic-string-creation-in-plpgsql-tp5785421p5785523.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] argument of CASE/WHEN must not return a set

[George Weaver]

Good morning,

I've have solved my problem in another way, but I am curious as to why I am 
getting the following error.

The following returns a boolean value a expected:

development=# SELECT LENGTH(ARRAY_TO_STRING(  
REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(#, ',')
development(#)  =
development-#LENGTH('12-70510')
development-#
development-# AS "12-70510";
 12-70510

 f
(1 row)

But if I put the comparison into a CASE or WHERE clause I get this error:

development=# SELECT CASE
development-# WHEN LENGTH(ARRAY_TO_STRING(  
REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(# , ',')
development(# )
development-#  = LENGTH('12-70510')
development-#
development-# THEN ARRAY_TO_STRING(  
REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+')
development(#  , ',')
development-#
development-# ELSE ''
development-# END AS "12-70510";
ERROR:  argument of CASE/WHEN must not return a set
LINE 2: WHEN LENGTH(ARRAY_TO_STRING(  REGEXP_MATCHES('12...


What am I missing?

Thanks,
George

Re: [GENERAL] Last inserted row id with complex PK

[Nelson Green]

On Wed, Jan 8, 2014 at 10:09 AM, Francisco Olarte wrote:

> Hi Nelson:
>
> On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green 
> wrote:
> > I have a projects log table with a three column PK, project_num,
> person_num,
> > and sequence, where each new entry for a project/person combination
> > increments the sequence, which is not an auto incrementing sequence. Is
> > there any way to retrieve the last entry to the table? For instance, if
> the
> > last entry for person 427 on project 15 was sequence number 125, is that
> > information available to me anywhere?
>
> SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
> project_num=15
>

Hi Francisco,

My apologies, I was not completely clear. I will not know any of the
columns in advance. The most recent insert is the result of user input from
a web form, so I won't know what project or what user generated the last
insert. That was why I wandered if that information was stored anywhere in
the system.


>
> If it's a PK it should have an index and I believe it will be quite fast.
>
> AAMOF, you could even build the above with max+1 into the insert query
> for a new entry and have the values returned using a RETURNING clause.
>
> Francisco Olarte.
>

Re: [GENERAL] Last inserted row id with complex PK

[Nelson Green]

On Wed, Jan 8, 2014 at 10:22 AM, Tom Lane  wrote:

> Francisco Olarte  writes:
> > Hi Nelson:
> > On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green 
> wrote:
> >> I have a projects log table with a three column PK, project_num,
> person_num,
> >> and sequence, where each new entry for a project/person combination
> >> increments the sequence, which is not an auto incrementing sequence. Is
> >> there any way to retrieve the last entry to the table? For instance, if
> the
> >> last entry for person 427 on project 15 was sequence number 125, is that
> >> information available to me anywhere?
>
> > SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
> > project_num=15
>
> Note that this will not work terribly well if there are concurrent
> insertions for the same person/project.  If that's not an issue, though,
> it should be fine.  It may be OK even if there are sometimes concurrent
> insertions, if you are prepared to retry duplicate-key failures.
>

Concurrency is not likely to be an issue for this system, but I would
certainly plan for it since I can not rule it out.


>
> > If it's a PK it should have an index and I believe it will be quite fast.
>
> It will be fast as long as sequence is the low-order column in the index.
>
> regards, tom lane
>

Re: [GENERAL] Consistent file-level backup of pg data directory

[Jeff Janes]

On Wed, Jan 8, 2014 at 7:09 AM,  wrote:

> On 2014-01-07 21:54, Jeff Janes wrote:
> > On Tue, Jan 7, 2014 at 10:58 AM,  > > wrote:
> > - keep a copy "backup_label" under a different name
> [...[
> > Why under a different name?  That sounds dangerous to me.
> ... otherwise it would be useless, because "pg_stop_backup"
> will delete the "backup_label" file
>

That is one of the reasons you should run the backup before you tell
PostgreSQL that you finished the backup :)


> > - call pg_stop_backup()
> > - the run the regular backup procedure
> >
> >
> > You have this order backwards.  You can only tell postgres that you are
> > done doing the backup once you are done doing the backup.
> this is what I tried 1st - unfortunately, this won't work at all,
> because postgres creates another checkpoint _after_ pg_stop and will
> refuse to run the restore procedure if this last wal file is missing
> (which in my scenario inevitably will be the case)
>

Eventually another checkpoint will be run, but pg_stop_backup will not
directly cause one to happen.  What it creates is a record in the WAL
stream saying that the backup has finished.  That record is what it needs
to see.  After writing that record, it triggers a log switch (not the same
thing as a checkpoint) so that the xlog file containing the record will get
archived (if you are were truly using archiving).

That requirement is there to protect your data, you cannot safely
circumvent the need for it.  If you have backed up the xlog file that
contains that record before the record was present, then that will be a
problem, as it won't find the record. The solution for that (other than
using archiving) is to backup everything before calling pg_stop_backup,
then backup just the pg_xlog after calling pg_stop_backup.



> > It is pretty much a disaster.  Do you really need rsync?  Otherwise, use
> > pg_basebackup, it is easier to get right.
>
> ... it looks like I didn't explain clear enough, what I am trying to
> accomplish. So let me try it again:
>
> We have a general backup procedure, that runs every night. For
> some machines, there may additionally be more
> specialized/fine-grained measures, but in any case, it should be
> possible to restore any given server without any machine-specific
> knowledge from the last nightly backup following a
> generic "restore-recipe". Our current rsync-based solution is a
> pretty good compromise between many different requirements.
>
> For machines running database systems, this means, this means,
> that I need some way to get a consistent state of some point in
> time. It does not particularly matter, which time exactly (in
> particular, I do not care, if transactions committed during the
> backup are included or not) For this purpose, I can define
> machine-specific hook to run before/ after the actual backup
> procedure.
>

I think it would be easier to just exclude the database from the
system-wide backup and use a different method for it, rather than engineer
the necessary before/after hooks onto the system-wide backup.



>
> Unfortunately, it does not look like there is any direct way to
> accomplish this with postgres except shutting down the whole
> database system while the backup is running. The systems will
> be almost idle while the backup runs, and write transactions
> will be very rare, but a total shutdown for every backup still
> would be too radical.
>
> Actually, with some tests copying the database directory while
> writing data to the database and then restarting postgres with
> such a copy, I did not manage to produce any visible
> inconsistencies. If postgres was able to restore the last
> consistent state in such a situation, then no additional measures
> would be needed (like I said, it is not particularly important
> which state this is - losing everything written during the last
> 15 minutes including committed transactions would be good enough)
> .But at least as far as I can tell from the documentation, this
> can not safely be assumed.
>

On small, nearly idle systems, you can often get away with doing a lot of
dangerous things.  This is perhaps unfortunate, as it breeds bad habits.


> Otherwise, the procedures for "Continuous Archiving" are clearly
> made for some a diffent purpose, it seems like this is about as
> close as it gets to what I need. As far as I understand:
>
> - between pg_start_backup() and pg_stop_backup() it should be safe
> to copy the data directory any time
> - all committed transactions during this time will go to the wal files
> (actually, I noticed that files in other directories than pg_xlog are
> modified, too ...)
>

True, but the data in pg_xlog tells it how to replay those other changes to
those other directories.  So it doesn't matter whether the other file was
backed up pre-modification or post-modification, as the WAL allows it to be
repaired either way. Unless the WAL stream ends too early--then it can't
repair them.


> - norm

Re: [GENERAL] Last inserted row id with complex PK

[David Johnston]

Nelson Green wrote
> My apologies, I was not completely clear. I will not know any of the
> columns in advance. The most recent insert is the result of user input
> from
> a web form, so I won't know what project or what user generated the last
> insert. That was why I wandered if that information was stored anywhere in
> the system.

I'd probably add either (or both) a table-level auto-sequence field and a
"recordcreationdate" default timestamptz field.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Last-inserted-row-id-with-complex-PK-tp5785863p5785901.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] Last inserted row id with complex PK

[Nelson Green]

On Wed, Jan 8, 2014 at 1:24 PM, David Johnston  wrote:

> Nelson Green wrote
> > My apologies, I was not completely clear. I will not know any of the
> > columns in advance. The most recent insert is the result of user input
> > from
> > a web form, so I won't know what project or what user generated the last
> > insert. That was why I wandered if that information was stored anywhere
> in
> > the system.
>
> I'd probably add either (or both) a table-level auto-sequence field and a
> "recordcreationdate" default timestamptz field.
>

Yeah, default timestamp was the best solution I could come up with, but I
was hoping there was a way to access the actual PK of the most recent
insert.
Looks like timestamp it is.

Thanks everyone!


> David J.
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Last-inserted-row-id-with-complex-PK-tp5785863p5785901.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] general questions

[CS DBA]

Hi All;

I recently ran into the following, any thoughts?

Thanks in advance...

1) \d and schema's
- I setup 2 schema's (sch_a and sch_b)
- I added both schema's to my search_path
- I created 2 tables:  sch_a.test_tab and sch_b.test_tab

If I do a \d with no parameters I only see the first test_tab table 
based on the order of my search_path.
I get that any queries will use the first found table if I don't specify 
the schemaname but
if I'm looking for a full list (i.e. \d with no parameters) I would 
think I should get a full list back


Is this intentional?


2) SET work_mem = x
It seems that any user can run set work_mem = x in a session. While this 
is keen for
folks who know what they are doing, it may not be so keen for folks who 
tend to do
foolish things, especially if a team has several of these types of folks 
on board
i.e. I can Imagine 5 devs all setting work_mem to 5GB each and running 
giant runaway

queries all on a dev server with 8GB of RAM.

Is there a way to restrict this?

3) Can I force unaligned mode AND no wrap for psql output?

4) Is there a way to know for sure ifa sql file was run in single 
transaction mode (after the fact), i.e. something in the logs?


5) Is there a query that will show me the actual prepared SQL text for a 
prepared query?






--
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] general questions

[Tom Lane]

CS DBA  writes:
> 1) \d and schema's
> - I setup 2 schema's (sch_a and sch_b)
> - I added both schema's to my search_path
> - I created 2 tables:  sch_a.test_tab and sch_b.test_tab

> If I do a \d with no parameters I only see the first test_tab table 
> based on the order of my search_path.
> I get that any queries will use the first found table if I don't specify 
> the schemaname but
> if I'm looking for a full list (i.e. \d with no parameters) I would 
> think I should get a full list back

> Is this intentional?

Yes.  If you want to see stuff that's invisible in your current search
path, use "\d *.*".  That's even documented somewhere ...

> 2) SET work_mem = x
> It seems that any user can run set work_mem = x in a session.

Yup.  If a user can issue arbitrary SQL, they can drive your server into
the ground with or without that, so I see little point in restricting it.
(Indeed, restricting it could be counterproductive, since too *small*
a value can be just as bad for performance as too large.)

> 3) Can I force unaligned mode AND no wrap for psql output?

[ shrug ... ]  Dunno, read the manual.

> 4) Is there a way to know for sure ifa sql file was run in single 
> transaction mode (after the fact), i.e. something in the logs?

If you're logging individual statements, there will be BEGIN and
COMMIT logged.  If you're not, I doubt the log will even tell you
a sql file was run, let alone such details.

> 5) Is there a query that will show me the actual prepared SQL text for a 
> prepared query?

select * from pg_prepared_statements

regards, tom lane


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

Re: [GENERAL] Last inserted row id with complex PK

[Alban Hertroys]

On 08 Jan 2014, at 16:54, Nelson Green  wrote:

> I have a projects log table with a three column PK, project_num, person_num, 
> and sequence, where each new entry for a project/person combination 
> increments the sequence, which is not an auto incrementing sequence. Is there 
> any way to retrieve the last entry to the table? For instance, if the last 
> entry for person 427 on project 15 was sequence number 125, is that 
> information available to me anywhere?

I think the answer rather depends on what you mean with this last inserted row 
and for what purpose you need it.

If you want that information right after it’s been inserted (for example 
because you need that information in an FK relation), you can use 
INSERT..RETURNING to return the values of the relevant PK fields.

If that’s not what you’re after, then what is it that determines which record 
is the “last one”? If you can’t identify such a record from your data while you 
need that information, then something is missing from your model.

If you’re planning to add such information to your model based on your current 
data, you might be able to get somewhat reliable results looking at the 
transaction xid’s that the records were created in. There are a number of 
pitfalls to that approach though, the most obvious one being transaction xid 
wraparound. Of course, multiple inserts from the same transaction would 
(originally) have the same xid, so you wouldn’t be able to determine which one 
of those would be the latest (unless they’re for the same person/project, 
obviously).
Such information could then be used to add a field with, for example, an 
incrementing sequence.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find 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] general questions

[Raghavendra]

On Thu, Jan 9, 2014 at 5:04 AM, Tom Lane  wrote:

> CS DBA  writes:
> > 1) \d and schema's
> > - I setup 2 schema's (sch_a and sch_b)
> > - I added both schema's to my search_path
> > - I created 2 tables:  sch_a.test_tab and sch_b.test_tab
>
> > If I do a \d with no parameters I only see the first test_tab table
> > based on the order of my search_path.
> > I get that any queries will use the first found table if I don't specify
> > the schemaname but
> > if I'm looking for a full list (i.e. \d with no parameters) I would
> > think I should get a full list back
>
> > Is this intentional?
>
> Yes.  If you want to see stuff that's invisible in your current search
> path, use "\d *.*".  That's even documented somewhere ...
>
>
As Tom already said, am adding document pointer, you can find i
n "patterns"
.

http://www.postgresql.org/docs/9.3/static/app-psql.html


>  > 3) Can I force unaligned mode AND no wrap for psql output?
>
>
For both unaligned
AND
no wrap, I guess you need to take help of PAGER and
"
psql
-A
" or "
p
ostgres=#
\a
"
or
"postgres=#
\pset format unaligned
"

I would try like:

export PAGER='less -RSX'// It no wraps the output
psql -A

// Unaligned

--
Raghav

EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Re: [GENERAL] Last inserted row id with complex PK

[Sameer Kumar]

On Wed, Jan 8, 2014 at 11:54 PM, Nelson Green wrote:

> I have a projects log table with a three column PK,
> project_num, person_num, and sequence, where each new entry for a
> project/person combination increments the sequence, which is not an auto
> incrementing sequence. Is there any way to retrieve the last entry to the
> table? For instance, if the last entry for person 427 on project 15 was
> sequence number 125, is that information available to me anywhere?



Are these
project_num, person_num FK from some other table? If yes then I would first
insert in those tables and use it over here?

Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD. *101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).
<>

Re: [GENERAL] How to know server status variable in postgresql?

[Sameer Kumar]

On Wed, Jan 8, 2014 at 7:22 PM, ambilalmca  wrote:

> I want to collect,
>
> *Connections. *
>
> Current connections. The number of currently open connections.
> Connections executing requests. The number of currently open connections
> that are executing requests.
> Idle connections. The number of currently idle connections.
> Max connections. The maximum number of concurrent connections to the
> database server.
> Used connections. Connections used as a percentage of maximum connections.
>
> *Buffers. *
>
> Shared buffers size. Current size of shared buffers.
> Disk cache size. Current size of disk cache buffer.
> Sort buffer size. Current size of sort buffer.
> Work buffer size. Current size of working requests buffer.
> Temp buffer size. Current size of temporary buffer.
>
> *IO Requests.*
>
> Blocks read. Number of blocks directly read on disk.
> For optimal performance this value should be the smallest possible. If the
> database has to execute too many disk accesses, performance will suffer.
> Index blocks read. Number of index blocks directly read on disk.
> % Index blocks read. Percentage of index blocks directly read on disk.
> Sequence blocks read. Number of sequence blocks directly read on disk.
>
> *Cache*
> Blocks read. Number of cached blocks read.
> Index blocks read. Number of cached index blocks read.
> % Index blocks read. Percentage of cached index blocks read.
> For optimal performance, this value must be as large as possible. If an
> insufficient number of index blocks are declared in the table, it could
> negatively impact the database server performance.
> Sequence blocks read. Number of cached sequence blocks read.
>
> *Index*.
>
> Index read. Number of reads initiated by an index.
> Indexed rows read. Number of rows read by indexed requests.
> Indexed rows fetched. Number of live rows fetched by indexed requests.
>
> *Command Rates.*
>
> Rows read. Number of rows read.
> Rows fetched. Number of rows fetched.
> Inserted rows. Number of rows inserted.
> Updated rows. Number of rows updated.
> Deleted rows. Number of rows deleted.
> Committed transactions. Number of committed transactions.
> This value should be relatively stable, indicating that there are no
> performance-reducing load peaks. If applications do not commit often
> enough,
> it will lead to an overload on the database server.
> Rolled back transactions. Number of transactions rolled back.
> % Rolledback transactions. Percentage of transactions rolled back.
>
> *Locks. *
>
> Locks waiting. Number of locks waiting.
> Locks held. Number of locks held.
> Process holding locks. Number of processes holding locks.
>
> how to collect these details by using query. now i find queries for
> sonnections. but i dont know anout others. please help me.@Sameer Kumar
>
>
>
>
Are you building your own scripts for monitoring the database?
Are are open source plug-ins available for that. Anyways, take a look at
this documentation:

http://www.postgresql.org/docs/9.2/static/monitoring-stats.html


You will find most of the things you have asked for (I guess all of it). If
you are not able to find something then you can post here.



> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/How-to-know-server-status-variable-in-postgresql-tp5785677p5785833.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] Last inserted row id with complex PK

[John R Pierce]

On 1/8/2014 10:14 AM, Nelson Green wrote:



On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green
mailto:nelsongree...@gmail.com>> wrote:
> I have a projects log table with a three column PK, project_num,
person_num,
> and sequence, where each new entry for a project/person combination
> increments the sequence, which is not an auto incrementing
sequence. Is
> there any way to retrieve the last entry to the table? For
instance, if the
> last entry for person 427 on project 15 was sequence number 125,
is that
> information available to me anywhere?

SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
project_num=15


Hi Francisco,

My apologies, I was not completely clear. I will not know any of the 
columns in advance. The most recent insert is the result of user input 
from a web form, so I won't know what project or what user generated 
the last insert. That was why I wandered if that information was 
stored anywhere in the system.


wait, so you just want the latest record inserted with -any- 
project/person ?   add a timestamptz field to your table with default 
current_timestamp;





--
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] returning json object with subset of keys

[Raphael Bauduin]

On Wed, Jan 8, 2014 at 4:05 PM, Merlin Moncure  wrote:

> On Wed, Jan 8, 2014 at 6:37 AM, Raphael Bauduin  wrote:
> > Hi
> >
> > I'm using the json functionalities of postgresql 9.3.
> > I have a query calling json_populate_recordset like this:
> >   json_populate_recordset(null::product, event->'products')
> > but it returns an error:
> > ERROR:  cannot call json_populate_recordset on a nested object
> >
> > There is indeed one key in event->'products' giving access to an array of
> > objects.
> >
> > Is there a way to specify which keys to keep from the object? I haven't
> > found ti in the docs.
> >
> > Here is pseudo code of what I'd like to do:
> >   json_populate_recordset(null::product, event->'products' WITH ONLY KEYS
> > {'f1','f2'})
>
> unfortunately, not without manipulating the json.  this is basically a
> somewhat crippling limitation of the json_populate functions -- they
> can't handle anything but flat tuples.  so you have to do something
> highly circuitous.
>
> problem (one record):
> postgres=# create table foo(a text, b text);
> postgres=# select json_populate_record(null::foo, '{"a": "abc", "b":
> "def", "c": [1,2,3]}'::json);
> ERROR:  cannot call json_populate_record on a nested object
>
> nasty solution:
> postgres=# with data as (select '{"a": "abc", "b": "def", "c":
> [1,2,3]}'::json as j)
> select json_populate_record(null::foo, row_to_json(q)) from
> (
>   select j->'a' as a, j->'b' as b from data
> ) q;
>  json_populate_record
> --
>  (abc,def)
>
> with some extra manipulations you can do a record set. basically, you
> need to get the json 'right' first (or that can be done on the
> client).
>
> merlin
>


ok, thanks for your reply.
Is this considered to be added in the future to the json functions
available? I could use it frequently I think.

Cheers

raph