Re: [GENERAL] Expression to construct a anonymous record with named columns?

[Alban Hertroys]

On 20 Sep 2012, at 20:36, Benedikt Grundmann wrote:

> So named anonymous records / row types seem to be strangely second class.  
> Can somebody clarify the restrictions and rationale or even better show a way 
> to do the equivalent of (made up syntax ahead):
> 
> select row(1 as a, 2 as b);

select * from (values (1, 2, 3)) a (a, b, c);

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.



-- 
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 psql -f to load a UTF8 file

[Chris Angelico]

On Fri, Sep 21, 2012 at 2:39 PM, John R Pierce  wrote:
> On 09/20/12 10:27 AM, Alan Millington wrote:
>>
>> I am using Notepad, which inserts the byte order mark. Following the links
>> a bit further, I gather that the version of Notepad that I am using may not
>> identify a UTF8 file correctly if the byte order mark is omitted. Also, as I
>> mentioned, Python makes use of it. (From the Python documentation on
>> Encoding declarations: "If the first bytes of the file are the UTF-8
>> byte-order mark ('\xef\xbb\xbf'), the declared file encoding is UTF-8 (this
>> is supported, among others, by Microsoft’s Notepad).")
>
> I've never seen Notepad generate UTF8.   Usually its either 8 bit ASCII
> (ISO8559-1 or something), or its UTF16 aka "Unicode".

Those are the defaults; you can tell it to save as UTF-8.

But the general advice is: Don't use Notepad! It can't handle Unix
newlines either (something which annoys me periodically when I'm on a
borrowed Windows machine and need to view a file quickly). There are
many better editors around; Notepad++ was mentioned, and NoteTab is
another good one. My personal preference is SciTE, available for Linux
as well as Windows. You'll start to realize how handy syntax
highlighting is when your next bug is caught even before you save,
because the apostrophe in the quoted string breaks the colorization.
Anything that reduces debugging time can't be a bad thing!

ChrisA


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

Re: [GENERAL] Using psql -f to load a UTF8 file

[John R Pierce]

On 09/20/12 10:27 AM, Alan Millington wrote:
I am using Notepad, which inserts the byte order mark. Following the 
links a bit further, I gather that the version of Notepad that I am 
using may not identify a UTF8 file correctly if the byte order mark is 
omitted. Also, as I mentioned, Python makes use of it. (From the 
Python documentation on Encoding declarations: "If the first bytes of 
the file are the UTF-8 byte-order mark ('\xef\xbb\xbf'), the declared 
file encoding is UTF-8 (this is supported, among others, by 
Microsoft’s Notepad).")


I've never seen Notepad generate UTF8.   Usually its either 8 bit ASCII 
(ISO8559-1 or something), or its UTF16 aka "Unicode".




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




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

Re: [GENERAL] foreign key from array element

[Chris Travers]

On Thu, Sep 20, 2012 at 12:18 AM, Gabriele Bartolini <
gabriele.bartol...@2ndquadrant.it> wrote:

> Hi Chris,
>
>thank you very much for taking the time to read the article and get
> into the features proposed with our patch.


You are welcome.  Also in case there is ambiguity, the feature I was
describing animosity towards was table inheritance.  I have seen people
advocate getting rid of the feature altogether but it is really useful for
a set of problems out there.  The problem of course is that in its current
form it is a bit of a dangerous feature.

>
>>
> I agree with you that this feature won't (and probably shouldn't) change
> modelling approaches in the majority of the cases. But will bring new
> opportunities, therefore make PostgreSQL even more versatile. I still
> believe that in some cases - not just indistinctively - aggregation in
> object oriented modelling can definitely be logically modelled using
> arrays, with referential integrity guaranteed by this feature.


BTW, I don't know if you have seen the series I have been doing on
Object-Relational modelling in PostgreSQL but if you haven't,
http://ledgersmbdev.blogspot.com/  (right now there are 9 posts up with an
epilogue coming).

I cover a lot of "dangerous" features--- composite types in columns,
non-1NF designs, table inheritance.  Particularly the nested storage post
might be interesting in terms of both uses and misuses of this proposed
feature.

In fact it occurs to me that the main thing it buys is an ability to do
subset constraints on the foreign key set gracefully, for example, ensuring
that there are between 5 and 10 foreign keys referenced in a specific case
or the like.

>
>
>  However, after thinking about the feature overnight, I can see a
>> number of use cases for it, ranging from recording something like race
>> results (where update contention is definitionally not an issue
>> because the record of an event aren't supposed to change) to sanity
>> checks in materialized views, and there are probably additional uses
>> that are not apparent yet.
>>
>
> I totally agree with you. This is exactly what we (as a community) need to
> do now as far as this feature is concerned. We need to have a larger use
> base and from there fully understand what the community needs. For
> instance, for 9.2 we had already developed actions on update and delete
> operations - assuming generic use cases. We have preferred for now to take
> out that part and start with a simpler patch where actions are forbidden.
> Through community feedback we found a name for the feature that was
> commonly accepted (we had called them EACH FOREIGN KEYS last year), and
> came up with an easy to understand syntax (and a better naming). It was
> important not to go too far down an unexplored territory. :)
>
>
I think the problem for the cascade and set null operations is determining
the behavior to be defined.  would ON DELETE CASCADE delete the value from
the array or would it delete the whole row?  What about ON DELETE SET NULL?
 Do we change the value in the array to NULL or just remove it from the
array?  So I think for now that's sane.

I think in terms of community, the object-relational features do need more
exposure, and more attention generally.  Part of the reason I started
blogging about them was to bring more attention to them, and try to help
get more exposure to the current costs and benefits of using them.  If
people are pushing the boundaries a bit more, I think a lot of things will
get improved upon.

Best Wishes,
Chris Travers

Re: [GENERAL] Using psql -f to load a UTF8 file

[Alan Millington]

Thank you for the link. I am using Notepad, which inserts the byte order mark. 
Following the links a bit further, I gather that the version of Notepad that I 
am using may not identify a UTF8 file correctly if the byte order mark is 
omitted. Also, as I mentioned, Python makes use of it. (From the Python 
documentation on Encoding declarations: "If the first bytes of the file are the 
UTF-8 byte-order mark ('\xef\xbb\xbf'), the declared file encoding is UTF-8 
(this is supported, among others, by Microsoft’s Notepad).")
 
The conclusion seems to be that I must use one editor for Python, and another 
for Postgres.
 



From: Leif Biberg Kristensen 
To: Postgres general mailing list  
Cc: Alan Millington  
Sent: Thursday, 20 September 2012, 16:44
Subject: Re: [GENERAL] Using psql -f to load a UTF8 file

Torsdag 20. september 2012 16.56.16 skrev Alan Millington :
> psql". But how am I supposed to remove the byte order mark from a UTF8
> file? I thought that the whole point of the byte order mark was to tell
> programs what the file encoding is. Other programs, such as Python, rely
> on this.

http://en.wikipedia.org/wiki/Byte_order_mark

While the Byte Order Mark is important for UTF-16, it's totally irrelevant to 
the UTF-8 encoding. Still you'll find several editors that automatically input 
BOMs in every text file. There is usually a setting "Insert Byte Order Mark" 
somewhere in the configuration, and it may be on by default.

regards, Leif

[GENERAL] Expression to construct a anonymous record with named columns?

[Benedikt Grundmann]

Hello,

Is there a way to construct write an expression that constructs a record
with with named columns.  Specificially without the need for a
corresponding named type.

That is

postgres=# select row(1, 2, 3);
   row
-
 (1,2,3)
(1 row)

Creates a unnamed record type.  And indeed it is for example not possible
to expand it:

postgres=# select (row(1, 2, 3)).*;
ERROR:  record type has not been registered

On the other hand columns listed in a multi column select clause create a
row type that is expandable and named:
postgres=# select ((bar.*).x).a from (select x from (select 1 as a, 2 as b)
x) bar;
 a
---
 1
(1 row)

But it seems to not be possible to do so without a from clause:

postgres=# select ((select x from (select 1 as a, 2 as b) x)).a;
ERROR:  syntax error at or near "."
LINE 1: select ((select x from (select 1 as a, 2 as b) x)).a;
  ^
postgres=# select ((select x from (select 1 as a, 2 as b) x)).*;
ERROR:  syntax error at or near "."
LINE 1: select ((select x from (select 1 as a, 2 as b) x)).*;

So named anonymous records / row types seem to be strangely second class.
Can somebody clarify the restrictions and rationale or even better show a
way to do the equivalent of (made up syntax ahead):

select row(1 as a, 2 as b);

Cheers,

Bene

Re: [GENERAL] Using psql -f to load a UTF8 file

[Chris Angelico]

On Fri, Sep 21, 2012 at 11:21 AM, Craig Ringer  wrote:
> I strongly disagree. The BOM provides a useful and standard way to
> differentiate UTF-8 encoded text files from the random pile of encodings
> that any given file could be.

The only reliable way to ascertain the encoding of a hunk of data is
with something out-of-band. Relying on the first three bytes being
\xEF\xBB\xBF is not much more reliable than detecting based on octet
frequency, which is what leads to the "Bush hid the facts" hack in
Notepad. This is why many Internet protocols have metadata carried
along with the file (eg Content-type in HTTP), rather than relying on
internal evidence.

> psql should accept UTF-8 with BOM.

However, this I would agree with. It's cheap enough to detect, and
aside from arbitrarily trying to kill Notepad (which won't happen
anyway), there's not a lot of reason to choke on the BOM. But it's not
a big deal.

ChrisA


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

Re: [GENERAL] Using psql -f to load a UTF8 file

[Craig Ringer]

On 09/20/2012 11:44 PM, Leif Biberg Kristensen wrote:

  Torsdag 20. september 2012 16.56.16 skrev Alan Millington :

psql". But how am I supposed to remove the byte order mark from a UTF8
file? I thought that the whole point of the byte order mark was to tell
programs what the file encoding is. Other programs, such as Python, rely
on this.


http://en.wikipedia.org/wiki/Byte_order_mark

While the Byte Order Mark is important for UTF-16, it's totally irrelevant to
the UTF-8 encoding.


I strongly disagree. The BOM provides a useful and standard way to 
differentiate UTF-8 encoded text files from the random pile of encodings 
that any given file could be.


On many platforms (including all Windows versions) the default system 
text encoding for 8-bit text is not UTF-8. On such systems, a BOM in a 
UTF-8 file allows a program/editor to reliably work out that it's UTF-8 
and treat it as such, rather than mangling it by interpreting it as the 
local system encoding.


psql should accept UTF-8 with BOM.

--
Craig Ringer


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

Re: [GENERAL] Why do I have holes in my pages?

[Aleksey Tsalolikhin]

On Thu, Sep 20, 2012 at 1:53 PM, John R Pierce  wrote:
> On 09/20/12 1:34 PM, Aleksey Tsalolikhin wrote:
>>
>> Right on.  I got that out of my pgstatspack report.
>>
>> \l+ in psql tells me the same thing - 400 GB
>
>
> it might be interesting to see the output of...
>
> du -hs $PGDATA/*

Well, that was it!  Thanks, John!

2.3T/data/backups
400G/data/base

We store our pg_dumps on the same filesystem (they are copied off to
another server but we don't delete them) so it swelled the filesystem
size as reported by "df".

Sorry about that.  And thanks for the help!

Aleksey


-- 
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 do I have holes in my pages?

[John R Pierce]

On 09/20/12 1:34 PM, Aleksey Tsalolikhin wrote:

Right on.  I got that out of my pgstatspack report.

\l+ in psql tells me the same thing - 400 GB


it might be interesting to see the output of...

du -hs $PGDATA/*

(assuming this is a linux or similar unix system).  This will show 
how much space is being used by the various directories under the PG 
data directory.if pg_xlog is very large, you may have an issue with 
wal archiving or something.   if pg_log is very large, you may have an 
issue with A) too much being logged, and B) nothing cleaning up stale 
log files.


how did you arrive at the 2.7TB number?and what file system does 
this 6.6TB volume use?





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



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

Re: [GENERAL] Why do I have holes in my pages?

[Victor Yegorov]

Take a look at this part of the documentation:
http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY

The “missing” entries belong to the tuples that you have DELETEd/UPDATEd
and that are no longer visible
to your current session, but still might be for the others, that started
some time ago. When tuples are no longer
needed, VACUUM will “release” the slots by adding them into the
FreeSpaceMap.

Still, if you have “empty” slots in the middle of your datafiles, VACUUM
cannot resize files.
This leads to the fact that while database size is being not so big, actual
disk space occupied by it
is bigger. This is called “bloat”.

Check the output of the query here:
http://wiki.postgresql.org/wiki/Show_database_bloat

Also, having such a big difference in the reported and actual size of the
database, may I ask:
- when was the last time you performed VACUUM?
- don't you have autovacuum = on (which is default) in your configuration?


2012/9/20 Aleksey Tsalolikhin 

> On Thu, Sep 20, 2012 at 12:34 PM, Bill Moran 
> wrote:
> > In response to Aleksey Tsalolikhin :
> >>
> >> Current DB size is 400 GB and it takes up 2.7 TB (on a 6.6 TB
> filesystem).
> >
> > I expect that the first thing that others are going to ask
> > is "what is telling you that your DB is 400G?"
>
>
> Right on.  I got that out of my pgstatspack report.
>
> \l+ in psql tells me the same thing - 400 GB



-- 
Victor Y. Yegorov

Re: [GENERAL] Why do I have holes in my pages?

[Aleksey Tsalolikhin]

On Thu, Sep 20, 2012 at 12:34 PM, Bill Moran  wrote:
> In response to Aleksey Tsalolikhin :
>>
>> Current DB size is 400 GB and it takes up 2.7 TB (on a 6.6 TB filesystem).
>
> I expect that the first thing that others are going to ask
> is "what is telling you that your DB is 400G?"


Right on.  I got that out of my pgstatspack report.

\l+ in psql tells me the same thing - 400 GB


-- 
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 do I have holes in my pages?

[Bill Moran]

In response to Aleksey Tsalolikhin :
> 
> Current DB size is 400 GB and it takes up 2.7 TB (on a 6.6 TB filesystem).

I expect that the first thing that others are going to ask
is "what is telling you that your DB is 400G?"

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/


-- 
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 do I have holes in my pages?

[Aleksey Tsalolikhin]

Why do I have holes in my pages?

Postgres 8.4.12

 "select ctid from big_table" on my master shows that pages have
"holes" in them.

Here is example for page 431665:

 (431665,2)
 (431665,5)
 (431665,8)
 (431665,11)
 (431665,14)
 (431665,17)
 (431665,20)
 (431665,23)

Current DB size is 400 GB and it takes up 2.7 TB (on a 6.6 TB filesystem).

So what happened to rows 1, 3 and 4 and so on?

I have to size a database server for next year's budget, and I will
have to explain to my mgmt why we have 400 GB taking up 2.7 TB.  Help?
 Would appreciate a pointer to the appropriate section in the manual
if this is documented.

Thanks,
-at


-- 
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 psql -f to load a UTF8 file

[John R Pierce]

On 09/20/12 7:56 AM, Alan Millington wrote:
I discovered a long time ago that psql does not like UTF8 files: it 
complains about the byte order mark on the first line.


in case it wasn't clear from previous replies, Windows native Unicode 
format is NOT UTF8, its UTF16, where every character is stored as 2 bytes.




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



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

Re: [GENERAL] Using psql -f to load a UTF8 file

[Leif Biberg Kristensen]

 Torsdag 20. september 2012 16.56.16 skrev Alan Millington :
> psql". But how am I supposed to remove the byte order mark from a UTF8
> file? I thought that the whole point of the byte order mark was to tell
> programs what the file encoding is. Other programs, such as Python, rely
> on this.

http://en.wikipedia.org/wiki/Byte_order_mark

While the Byte Order Mark is important for UTF-16, it's totally irrelevant to 
the UTF-8 encoding. Still you'll find several editors that automatically input 
BOMs in every text file. There is usually a setting "Insert Byte Order Mark" 
somewhere in the configuration, and it may be on by default.

regards, Leif


-- 
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 psql -f to load a UTF8 file

[Adrian Klaver]

On 09/20/2012 10:44 AM, Leif Biberg Kristensen wrote:

  Torsdag 20. september 2012 19.27.22 skrev Alan Millington :

Thank you for the link. I am using Notepad, which inserts the byte order
mark. Following the links a bit further, I gather that the version of
Notepad that I am using may not identify a UTF8 file correctly if the byte
order mark is omitted. Also, as I mentioned, Python makes use of it. (From
the Python documentation on Encoding declarations: "If the first bytes of
the file are the UTF-8 byte-order mark ('\xef\xbb\xbf'), the declared file
encoding is UTF-8 (this is supported, among others, by Microsoft’s
Notepad).")
The conclusion seems to be that I must use one editor for Python, and
another for Postgres.


I would strongly advise against using Notepad for any kind of text 
editing.  Wordpad works better, or even better yet Notepad ++:


http://notepad-plus-plus.org/



It's been a long time since I last wrote a Python script, but I've always used
the explicit encoding directive:

#! /usr/bin/env python
# -*- encoding: utf-8 -*-

See http://docs.python.org/release/2.5.1/ref/encodings.html which also
mentions the BOM method as an alternative.

regards, Leif





--
Adrian Klaver
adrian.kla...@gmail.com


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

Re: [GENERAL] Using psql -f to load a UTF8 file

[Leif Biberg Kristensen]

 Torsdag 20. september 2012 19.27.22 skrev Alan Millington :
> Thank you for the link. I am using Notepad, which inserts the byte order
> mark. Following the links a bit further, I gather that the version of
> Notepad that I am using may not identify a UTF8 file correctly if the byte
> order mark is omitted. Also, as I mentioned, Python makes use of it. (From
> the Python documentation on Encoding declarations: "If the first bytes of
> the file are the UTF-8 byte-order mark ('\xef\xbb\xbf'), the declared file
> encoding is UTF-8 (this is supported, among others, by Microsoft’s
> Notepad).") 
> The conclusion seems to be that I must use one editor for Python, and
> another for Postgres. 

It's been a long time since I last wrote a Python script, but I've always used 
the explicit encoding directive:

#! /usr/bin/env python
# -*- encoding: utf-8 -*-

See http://docs.python.org/release/2.5.1/ref/encodings.html which also 
mentions the BOM method as an alternative.

regards, Leif


-- 
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 psql -f to load a UTF8 file

[Tom Lane]

Alan Millington  writes:
> I am running Postgres 8.4.1 on Windows XP Professional Service Pack 3. My 
> database is UTF8. I use psql -f to load files containing DDL and DML 
> commands. I discovered a long time ago that psql does not like UTF8 files: it 
> complains about the byte order mark on the first line. Up to now I have 
> worked round that by making sure that the files were saved as what Microsoft 
> calls "ANSI". However, that option is not available if I want to insert data 
> which includes non-ASCII characters.

FWIW, psql 9.0 and later will ignore an initial BOM if the client
encoding is UTF8.

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] Passing row set into PL/pgSQL function.

[Lucas Clemente Vella]

> http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

I have already seen this page, I am OK in running SERIALIZABLE
transactions, and have no problem in replaying failed transactions due
to race condition. Anyway, that is completely off my issue: I need
upsert and I am prepared to deal with it. I just want to save typing
by creating a reusable function.

-- 
Lucas Clemente Vella
lve...@gmail.com


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

[GENERAL] Using psql -f to load a UTF8 file

[Alan Millington]

I am running Postgres 8.4.1 on Windows XP Professional Service Pack 3. My 
database is UTF8. I use psql -f to load files containing DDL and DML commands. 
I discovered a long time ago that psql does not like UTF8 files: it complains 
about the byte order mark on the first line. Up to now I have worked round that 
by making sure that the files were saved as what Microsoft calls "ANSI". 
However, that option is not available if I want to insert data which includes 
non-ASCII characters.
 
I have found a suggestion that psql can be told to expect UTF8 files by 
creating a file called psqlrc.conf containing the line \encoding unicode. I 
have tried putting this file (i) in the Postgres data directory, along with 
postgresql.conf, and (ii) in %APPDATA%\postgresql, but I still get an error:
 
psql:120919_insert_into_letter.sql:1: ERROR:  syntax error at or near "insert
"
LINE 1: insert into LETTER_VAR (var_name, type) values ('REPORT_COP...
 
I have found a workaround, which is to start the file with a line containing 
just a semicolon. Then the empty "statement" fails, but the other statements 
succeed:
 
psql:120919_insert_into_letter_copy2.sql:1: ERROR:  syntax error at or near "
"
LINE 1: ;
    ^
INSERT 0 1
INSERT 0 1

 
However, I feel sure that there must be a better way.
Ihave noted BUG report #6271: psql -f reporting unexpected syntax errors on 
first command. This involves the same problem. Álvaro Herrera advised that "You 
need to remove [the byte order mark] before passing the file to psql". But how 
am I supposed to remove the byte order mark from a UTF8 file? I thought that 
the whole point of the byte order mark was to tell programs what the file 
encoding is. Other programs, such as Python, rely on this.

Re: [GENERAL] Passing row set into PL/pgSQL function.

[Merlin Moncure]

On Wed, Sep 19, 2012 at 4:37 PM, Lucas Clemente Vella  wrote:
> I am trying to write a generic "upsert" function in PL/pgSQL, in a way
> that I can specify the table were I want to insert/update, the columns
> whose values I want to specify, and the values to be inserted.
>
> So far I have come up with a solution whose signature is:
>
> CREATE OR REPLACE FUNCTION upsert(IN tname text, IN cnames text[],
> VARIADIC vals anyarray) RETURNS void
>
> Whose tname is the table, cnames are the columns ans vals the values.
> The problem I have is when I try to call the function: I can only pass
> values of a previously defined type, like:
>
> SELECT upsert('my_table', ARRAY['key', 'data'], (10,
> 'hello')::my_table, (20, 'world')::my_table);
>
> Instead of:
>
> SELECT upsert('my_table', ARRAY['key', 'data'], (10, 'hello'), (20, 'world'));
>
> What gives me the error:
>
> ERROR:  PL/pgSQL functions cannot accept type record[]

note, pl/pgsql functions can take arrays of non-anonymous record types
-- either tables, or composite types.  you're just not allowed to pass
anonymous rows in.

for key value pairs, also you should take a look at hstore.  You can
also make arrays of hstore.

merlin


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

[GENERAL] WITH RECURSIVE from 2 or more tables.

[Капралов Александр]

Hello.

I have 2 tables:

CREATE TABLE "group"
(
  id serial NOT NULL
  "name" character varying(23) NOT NULL
  id_user integer NOT NULL DEFAULT 0,
  parent integer DEFAULT 0,
  CONSTRAINT group_user_fkey FOREIGN KEY (id_user) REFERENCES "user"
(id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
)

and

CREATE TABLE "user"
(
  id serial NOT NULL
  "login" character varying(12) NOT NULL
  parent integer DEFAULT 0
)
Can I get a tree of records in a single query, and their two tables
using "WITH RECURSIVE".
tree one table I made, but how to combine these queries do not understand.

WITH RECURSIVE gg(id,parent,level,path,cycle) AS (
   SELECT id,parent,0,ARRAY[id],false FROM web."group" WHERE id=899
   UNION ALL
   SELECT g.id,g.parent,level + 1,path||g.id,g.id=ANY(path) FROM
web."group" as g,gg WHERE g.parent=gg.id AND NOT cycle
 )
 SELECT u.id,u.name,path FROM web."group" as u, gg WHERE gg.id=u.id;


WITH RECURSIVE uu(id,parent,level,path,cycle) AS (
   SELECT id,id_user,0,ARRAY[id],false FROM web."user" WHERE id=71
   UNION ALL
   SELECT u.id,u.id_user,level + 1,path||u.id,u.id=ANY(path) FROM
web."user" as u,uu WHERE u.id_user=uu.id AND NOT cycle
 )
 SELECT u.id,u.login,path FROM web."user" as u, uu WHERE uu.id=u.id;

Could you please help me.


-- 
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_upgrade: out of memory

[Tom Lane]

"Carrington, Matthew (Produban)"  writes:
> I have attempted to upgrade my Postgres installation this morning from 9.0.1 
> to 9.2.0 and it failed with an out of memory problem using pg_dumpall to dump 
> the first database.

Hm.  I'm not aware of any reason for 9.2 pg_dump to take hugely more
memory than 9.0.  How big is the database (how many objects)?  When
you run 9.0 pg_dump against it, how big does the process get?  (Watching
it in "top" is probably a close enough answer here.)

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] Need psql send email

[Chris Travers]

Hi all;

A couple points here.

First, you probably don't want to send email directly from a database
function.  This gives significant problems for which there is no good
solution.  Consider:

1)  You sent your email and now the transaction rolls back.  You *cannot*
roll back the sent email.

2)  Your email fails to send.  Do you abort the transaction?

IMO it is always better to send email from a second process that can be
notified on db commit.  This avoids these issues and kicks them to a
post-transaction handler.

As luck would have it, I recently set a project up on Google Code to help
address this (and other application integration) issues.  See
http://code.google.com/p/pg-message-queue/

There isn't a lot of overlap with something like pgq.  This is
listen/notify/queue tables based.  May not ever be big and professional but
it should work once the bugs are ironed out.  Even before then it may give
a good idea of how to implement a notification-based queue on PostgreSQL.

The idea here is that you can essentially send a message to a channel on a
db event (say, from a trigger) and then have another app that either
periodically checks the queue (say, from a cron job) or listens on a
channel for notifications.

The whole thing was confirmed working before I made some changes.  If folks
are interested in helping I am sure it will be well tested and working in
no time.  Please read the docs first though.  I wouldn't say it is
production-ready yet, but it may provide an overview of how to go about
implementing something like this in production.

Also for more info on how to do this with a LISTEN/NOTIFY approach outside
of the above, see
http://ledgersmbdev.blogspot.com/2012/09/objectrelational-interlude-messaging-in.html

In general I think mixing transactional and non-transactional side-effects
is just asking for trouble.  Don't do it any  more than you have to.

Best Wishes,
Chris Travers

Re: [GENERAL] Need psql send email

[Edson Richter]

Em 20/09/2012 09:07, pavithra escreveu:
Hi All, I am new to postgresql. I want to send email by using pl 
pgsql. I want to know how to set up the configurations for mail 
server. Can any one help me in solving this?. [hidden email] 



View this message in context: Need psql send email 

Sent from the PostgreSQL - general mailing list archive 
 
at Nabble.com.

Dear friend,

I don't know if it is possible. But my experience with MS SQL Server 
(integration with OutLook) introduces hundreds of flaws (including 
crashes) into the database.

How did I accomplish this task:

a) To notify backups and so, I've configured my Cron task to do that (it 
is fairly easy and well documented)


b) To notify about business tasks of my applications, my applications 
send the e-mail (in my case, I do use Java, so I use standard JavaMail 
API that does everything in a snap without any flaws for years now). I 
believe every language in the world has similar stable APIs for sending 
e-mails


c) If I need to send e-mail based on database events (like a trigger), I 
use a "Queue Table" where I insert messages that need to be sent, and 
have external application that (from time to time) checks this table for 
new messages to be sent.



I hope this ideas help you.

Regards,

Edson.

Re: [GENERAL] Need psql send email

[Martin French]

 > The 1st one seems OK in a scary-from-a-security-standpoint kind of way.

Agree, it needs to be weighed up and assessed from a security stand point 
I guess.
 
> The 2nd, not so much. See
>http://stackoverflow.com/questions/12002662/psql-trigger-send-email
> 
> Imagine if the DNS goes wonky. Do you want all your backends tied up in 
> DNS lookups? Or timing-out TCP connections?

Agree 100%, which is why I noted: "the server is properly configured on 
the network"... I suppose you could always provide an IP address as the 
mail host. This function is only a "Quick Knock Together" job, that works 
readily enough.

>

IMHO There's always an inherent risk with any form of sending mail from an 
RDBMS, whether it be abuse or otherwise, however; it's one of those 
situations where "needs must", and more often than not must be done 
quickly.

I would guess that having SMTP built into the DB engine itself would be no 
less susceptible to abuse or problems than any other method (For example 
UTL_SMTP in Oracle, which I've had hang before due to issues with SMTP 
servers).

I guess it's one of those where you just have to weigh up the options and 
choose the best one for your situation/application.

Cheers

Martin
=

Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham, 
NG7 2PZ
England

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=
===
E-mail: i...@romaxtech.com
Website: www.romaxtech.com
=


Confidentiality Statement
This transmission is for the addressee only and contains information that 
is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf 
of the addressee 
you may not copy or use it, or disclose it to anyone else. 
If you have received this transmission in error please delete from your 
system and contact the sender. Thank you for your cooperation.
=

Re: [GENERAL] Slow counting still true?

[Edson Richter]

Em 18/09/2012 15:24, Jeff Janes escreveu:

On Mon, Sep 17, 2012 at 9:14 AM, Edson Richter  wrote:


The wiki page in question has been updated today, and I see the alert in top
of page "Note that the following article only applies to versions of
PostgreSQL prior to 9.2. Index-only scans are now implemented."

So seems that traversing indexes for count(*) would be faster on 9.2, right?

Not really, as it still needs to visit some representation of every
tuple.  Now, if the entire index in is RAM while the table would not
be, it could be a lot faster.  But that is more of a special case than
a general one.


AFAIK, for count(*) doesn't matter the order data is stored - just need to
load index leaf pages and count from there, right?

That would only work if there was no concurrent activity.  If someone
else splits on index page, some of the entries on that page could move
to a location where they would get visited either zero times or two
times.
I see. This is were MS SQL Server escalates row locks into page locks, 
and get rid of the concurrency (at very expensive cost, IMHO).


Regards,

Edson



Cheers,

Jeff






--
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] Need psql send email

[Martin Gainty]

many is the time when spammers have used Open Relay SMTP servers to send their 
junk mail so i would advise against using sendmail on Open Relay SMTP servers
I would narrow access by SSH or open a secure tunnel thru your firewall to your 
own internal DatabaseManagementSystem/J2EEServer/ApacheHTTPServer then allow 
those scripts (PL-SQL or Perl or Java) to invoke sendmail to the SMTPMailServer 
inside the firewall

If you are sponsoring your own email-server and I hope you are DISALLOW OPEN 
RELAY
Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.


To: dep...@depesz.com
CC: pavithra@gmail.com; pgsql-general@postgresql.org; 
pgsql-general-ow...@postgresql.org
Subject: Re: [GENERAL] Need psql send email
From: martin.fre...@romaxtech.com
Date: Thu, 20 Sep 2012 13:40:58 +0100



> > Hi All,I am new to postgresql. I want to send email by using
pl 

> pgsql. I want

> > to know how to set up the configurations for mail server.Can
any one help me

> > in solving this?. pavithra@gmail.com

> 

> http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/

> 

> Best regards,

> 

> depesz



Alternatively:



CREATE OR REPLACE FUNCTION sendmail(p_from text, p_to
text, p_subject text, p_content text)

  RETURNS void AS

$BODY$

use strict;

use warnings;

my ($from, $to, $subject, $content) = @_;

 

open(MAIL, "|/usr/sbin/sendmail -t") or
die 'Cannot send mail';

print MAIL "From: $from\n";

print MAIL "To: $to\n";

print MAIL "Subject: $subject\n\n";

print MAIL "$content";

 

close(MAIL);

$BODY$

  LANGUAGE plperlu;





Works ok provided sendmail is configured. 



or:



CREATE OR REPLACE FUNCTION send_smtp(p_mail_host text,


   
   
p_from text,


   
   
p_to text, 

   
   
p_subject text,


   
   
p_content text,


   
   
p_timeout integer
DEFAULT 60, 

   
   
p_debug integer
DEFAULT 0, 

   
   
p_exactaddr integer
DEFAULT 1, 

   
   
p_skipbad integer
DEFAULT 1)

  RETURNS void AS

$BODY$

use strict;

use warnings;

use Net::SMTP;

no strict 'refs';



my ($host, $sender, $recipient, $subject, $body, $timeout,
$debug, $exact, $skipbad) = @_;

(!defined($host) || !($host)) && die 'No SMTP
host provided.';

(!defined($sender) || !($sender)) &&  die
'No sender address/name provided.';

(!defined($recipient) || !($recipient)) &&
 die 'No recipient address specified.';



my $mail = Net::SMTP->new(

   
   
Host => $host, 

   
   
Debug => $debug,

   
   
Timeout => $timeout,

   
   
ExactAddresses => $exact

   
) or die 'Net::SMTP->new()
Failed';



$mail->mail($sender);

$mail->recipient($recipient, { SkipBad => $skipbad
});



$mail->data();

$mail->datasend("MIME-Version: 1.0\n");

$mail->datasend("From:" . $sender . "\n");

$mail->datasend("To:" . $recipient .
"\n");

$mail->datasend("Reply-To: ". $sender
. "\n");

$mail->datasend("Subject:" . $subject
. "\n\n");

$mail->dataend();

$mail->quit();

$BODY$

  LANGUAGE plperlu;





Feel free to hack away as much as required. 



Both of these work fine provided PL/PerlU is installed and the server is
properly configured on the network, and that there is a valid SMTP mail
host to receive.



Cheers



Martin 



=



Romax Technology Limited

Rutherford House

Nottingham Science & Technology Park

Nottingham, 

NG7 2PZ

England



Telephone numbers:

+44 (0)115 951 88 00 (main)



For other office locations see:

http://www.romaxtech.com/Contact

=

===

E-mail: i...@romaxtech.com

Website: www.romaxtech.com

=





Confidentiality Statement

T

Re: [GENERAL] Need psql send email

[Craig Ringer]

On 09/20/2012 08:40 PM, Martin French wrote:


Both of these work fine provided PL/PerlU is installed and the server is
properly configured on the network, and that there is a valid SMTP mail
host to receive.


The 1st one seems OK in a scary-from-a-security-standpoint kind of way.

The 2nd, not so much. See
  http://stackoverflow.com/questions/12002662/psql-trigger-send-email

Imagine if the DNS goes wonky. Do you want all your backends tied up in 
DNS lookups? Or timing-out TCP connections?


BTW, pavithra, check out http://brandolabs.com/pgmail if you really want 
to do it in the database.


--
Craig Ringer


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

Re: [GENERAL] RFE: Column aliases in WHERE clauses

[David Johnston]

> 
> On Wed, Sep 19, 2012 at 11:15 PM, David Johnston 
> wrote:
> > I could maybe see something like the following having some value:
> >
> > SELECT inverse
> > FROM data
> > WHERE x<>0 AND inverse > .5
> > MACRO inverse (1/x)
> >
> 
> WITH macros AS (SELECT *,1/x AS inverse FROM data) SELECT inverse FROM
> macros WHERE x<>0 AND inverse > .5
> 

In your example the "macro" has to either be attached directly to the FROM
or be used as part of a sub-select; it is not a text substitution macro at
all.  The pre-processor upon encountering a macro, would simply replace all
identifiers (at the same level in the query) with "(expression)".

David J.




-- 
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] Passing row set into PL/pgSQL function.

[Craig Ringer]

On 09/20/2012 01:47 PM, Lucas Clemente Vella wrote:

http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/


I have already seen this page, I am OK in running SERIALIZABLE
transactions, and have no problem in replaying failed transactions due
to race condition. Anyway, that is completely off my issue: I need
upsert and I am prepared to deal with it. I just want to save typing
by creating a reusable function.


In that case, maybe you could have your function accept a `refcursor`?

DECLARE some_curs CURSOR FOR VALUES ('a',1), ('b',2), ('c',3);
SELECT funky_upsert('table', ARRAY['col1','col2'], 'some_curs');
CLOSE some_curs;

Internally it could fetch rows from the refcursor into record fields and 
do what it needed.


Personally I'd just do the work app-side.

--
Craig Ringer



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

Re: [GENERAL] Need psql send email

[Martin French]

> > Hi All,I am new to postgresql. I want to send email by using pl 
> pgsql. I want
> > to know how to set up the configurations for mail server.Can any one 
help me
> > in solving this?. pavithra@gmail.com
> 
> http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/
> 
> Best regards,
> 
> depesz

Alternatively:

CREATE OR REPLACE FUNCTION sendmail(p_from text, p_to text, p_subject 
text, p_content text)
  RETURNS void AS
$BODY$
use strict;
use warnings;
my ($from, $to, $subject, $content) = @_;
 
open(MAIL, "|/usr/sbin/sendmail -t") or die 'Cannot send mail';
print MAIL "From: $from\n";
print MAIL "To: $to\n";
print MAIL "Subject: $subject\n\n";
print MAIL "$content";
 
close(MAIL);
$BODY$
  LANGUAGE plperlu;


Works ok provided sendmail is configured. 

or:

CREATE OR REPLACE FUNCTION send_smtp(p_mail_host text, 
p_from text, 
p_to text, 
p_subject text, 
p_content text, 
p_timeout integer DEFAULT 60, 
p_debug integer DEFAULT 0, 
p_exactaddr integer DEFAULT 1, 
p_skipbad integer DEFAULT 1)
  RETURNS void AS
$BODY$
use strict;
use warnings;
use Net::SMTP;
no strict 'refs';

my ($host, $sender, $recipient, $subject, $body, $timeout, $debug, $exact, 
$skipbad) = @_;
(!defined($host) || !($host)) && die 'No SMTP host provided.';
(!defined($sender) || !($sender)) &&  die 'No sender address/name 
provided.';
(!defined($recipient) || !($recipient)) &&  die 'No recipient address 
specified.';

my $mail = Net::SMTP->new(
Host => $host, 
Debug => $debug,
Timeout => $timeout,
ExactAddresses => $exact
) or die 'Net::SMTP->new() Failed';

$mail->mail($sender);
$mail->recipient($recipient, { SkipBad => $skipbad });

$mail->data();
$mail->datasend("MIME-Version: 1.0\n");
$mail->datasend("From:" . $sender . "\n");
$mail->datasend("To:" . $recipient . "\n");
$mail->datasend("Reply-To: ". $sender . "\n");
$mail->datasend("Subject:" . $subject . "\n\n");
$mail->dataend();
$mail->quit();
$BODY$
  LANGUAGE plperlu;


Feel free to hack away as much as required. 

Both of these work fine provided PL/PerlU is installed and the server is 
properly configured on the network, and that there is a valid SMTP mail 
host to receive.

Cheers

Martin 

=

Romax Technology Limited
Rutherford House
Nottingham Science & Technology Park
Nottingham, 
NG7 2PZ
England

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=
===
E-mail: i...@romaxtech.com
Website: www.romaxtech.com
=


Confidentiality Statement
This transmission is for the addressee only and contains information that 
is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf 
of the addressee 
you may not copy or use it, or disclose it to anyone else. 
If you have received this transmission in error please delete from your 
system and contact the sender. Thank you for your cooperation.
=

Re: [GENERAL] Need psql send email

[pavithra]

I am more wondered where we need to give the port address and smtpserver.

Can you give me the details of these?.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Need-psql-send-email-tp5724700p5724705.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] Need psql send email

[hubert depesz lubaczewski]

On Thu, Sep 20, 2012 at 05:07:18AM -0700, pavithra wrote:
> Hi All,I am new to postgresql. I want to send email by using pl pgsql. I want
> to know how to set up the configurations for mail server.Can any one help me
> in solving this?. pavithra@gmail.com

http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] Need psql send email

[Raymond O'Donnell]

On 20/09/2012 13:07, pavithra wrote:
> Hi All, I am new to postgresql. I want to send email by using pl pgsql.
> I want to know how to set up the configurations for mail server. Can any
> one help me in solving this?. [hidden email]

Hi there,

It's not possible to send email directly from pl/pgsql; it might be
possible in the untrusted form of pl/perl, but I'm not sure.

A possible alternative would be to have an external process poll a queue
table, take its data from there and send the emails.

HTH,

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

Re: [GENERAL] Need psql send email

[Victor Yegorov]

Check this article:
http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/


2012/9/20 pavithra 

> Hi All, I am new to postgresql. I want to send email by using pl pgsql. I
> want to know how to set up the configurations for mail server. Can any one
> help me in solving this?. [hidden 
> email]
>



-- 
Victor Y. Yegorov

[GENERAL] Need psql send email

[pavithra]

Hi All,I am new to postgresql. I want to send email by using pl pgsql. I want
to know how to set up the configurations for mail server.Can any one help me
in solving this?. pavithra@gmail.com



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Need-psql-send-email-tp5724700.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] should I increase default_statistics_target

[Ondrej Ivanič]

Hi,

On 20 September 2012 20:49, AI Rumman  wrote:
> Using explain analyze of a large query I found that in every step there are
> a lot difference between the number of rows  between actual and estimated.
> I am using default_statistics_target 200. Should I increase it?

I would keep it at default level but I would increase it per column:
ALTER TABLE  ALTER  SET STATISTICS 

and you can do the same for index:

ALTER TABLE  ALTER COLUMN  SET STATISTICS 

(for function indexes you need to use \d in order to see "real" column name)

Finally, you need to run analyse on that table / column

-- 
Ondrej Ivanic
(ondrej.iva...@gmail.com)


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

[GENERAL] should I increase default_statistics_target

[AI Rumman]

Using explain analyze of a large query I found that in every step there are
a lot difference between the number of rows  between actual and estimated.
I am using default_statistics_target 200. Should I increase it?

Re: [GENERAL] application for postgres Log

[Arvind Singh]

thanx Laurenz,
 
But
 
Our CSV Log contains lot of statements like the following THREE lines. They 
appear exactly one after the other.
 
And they number in thousands for a Session (more than ten thousand)
 
 
2011-11-11 12:41:31.484 
IST,"agent1","pem",524,"localhost:2141",4ebccaa2.20c,754,"idle",2011-11-11 
12:41:30 IST,2/308,0,LOG,0,"statement: INSERT INTO pemdata.settings 
(""name"", ""setting"", ""unit"", ""server_id"") VALUES ('xmlbinary', 'base64', 
NULL, '1')""exec_simple_query, .\src\backend\tcop\postgres.c:900",""

2011-11-11 12:41:31.484 
IST,"agent1","pem",524,"localhost:2141",4ebccaa2.20c,755,"INSERT",2011-11-11 
12:41:30 IST,2/0,0,LOG,0,"duration: 0.000 ms""exec_simple_query, 
.\src\backend\tcop\postgres.c:1128",""

2011-11-11 12:41:31.484 
IST,"agent1","pem",524,"localhost:2141",4ebccaa2.20c,756,"INSERT",2011-11-11 
12:41:30 IST,2/0,0,LOG,0,"QUERY STATISTICS","! system usage stats:
! 0.00 elapsed 0.00 user 0.00 system sec
! [0.25 user 0.156250 sys total]","INSERT INTO pemdata.settings 
(""name"", ""setting"", ""unit"", ""server_id"") VALUES ('xmlbinary', 'base64', 
NULL, '1')",,"ShowUsage, .\src\backend\tcop\postgres.c:4305",""
 
Is there anything that we enabled, because they dont appear after that 
particular session.
 
The log file is uploaded at 
http://dl.dropbox.com/u/71964910/pg_log_with_lot_of_session.zip

 
 
arvind ps 
 

> It would indeed be divine intervention if fifty thousand had
> five zeros.
> 
> Other than that, I don't see anything special about that.
> A session can last pretty long.
> Maybe you can solve your mystery by looking at the log entries.
> They should tell you what was going on.
> 
> > Although it never reoccurs and luckily we had csv option on during
> that period.
> > 
> > Where should i report such findings
> 
> I don't think there is anything wrong.
> At least nothing database related.
> 
> > I have uploaded that Part of Log at
> http://dl.dropbox.com/u/71964910/pg_log_with_lot_of_session.zip
> 
> That looks like somebody turned on "log_statement_stats" for a spell.
> 
> Yours,
> Laurenz Albe
> 
  

Re: [GENERAL] foreign key from array element

[Gabriele Bartolini]

Hi Chris,

   thank you very much for taking the time to read the article and get 
into the features proposed with our patch.


On Tue, 18 Sep 2012 17:17:56 -0700, Chris Travers 
 wrote:

So those are the cautions and why I don't think a feature like this
is suitable for routine usage, but truth be told a lot of the
object-relational features are definitely not for routine usage and
make a mess of things if people use them just because they can.  I
use table inheritance and I totally understand a lot of people's
hostility towards this feature.  Again, anytime you break 1NF you
should probably have a really good reason.  I don't think this
changes here.


I agree with you that this feature won't (and probably shouldn't) 
change modelling approaches in the majority of the cases. But will bring 
new opportunities, therefore make PostgreSQL even more versatile. I 
still believe that in some cases - not just indistinctively - 
aggregation in object oriented modelling can definitely be logically 
modelled using arrays, with referential integrity guaranteed by this 
feature.



However, after thinking about the feature overnight, I can see a
number of use cases for it, ranging from recording something like 
race

results (where update contention is definitionally not an issue
because the record of an event aren't supposed to change) to sanity
checks in materialized views, and there are probably additional uses
that are not apparent yet.


I totally agree with you. This is exactly what we (as a community) need 
to do now as far as this feature is concerned. We need to have a larger 
use base and from there fully understand what the community needs. For 
instance, for 9.2 we had already developed actions on update and delete 
operations - assuming generic use cases. We have preferred for now to 
take out that part and start with a simpler patch where actions are 
forbidden. Through community feedback we found a name for the feature 
that was commonly accepted (we had called them EACH FOREIGN KEYS last 
year), and came up with an easy to understand syntax (and a better 
naming). It was important not to go too far down an unexplored 
territory. :)



So yeah, as far as the feature goes, as documented, I haven't tried
it fully yet (expect to do so this weekend), but it looks useful at
least in some cases.


Thank you. That's really much appreciated.

Cheers,
Gabriel
--
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it


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