date:20131126

[GENERAL] Autodocumenting plpgsql function

2013-11-26 Thread Rémi Cura

Hey all,
somebody knows of a way to autodocument plpgsql function, in a docxygen
style
(adding tags in comments for instance, or creating doc templates to fill).

It would really help to write the doc and maintain it.

Thanks,

Rémi-C

Re: [GENERAL] Wrap around id failure and after effects

2013-11-26 Thread Richard Huxton

On 26/11/13 07:15, Arun P.L wrote:

Hi all,

We had a wraparound failure in the db and most of the tables and data
were missing. So we have done a full vacuum in db and after that the
tables reappeared but now the problem is, all the tables have duplicate
when listing tables with /dt. And also after the vacuum we recievied the
following warning.

*INFO: free space map: 48 relations, 29977 pages stored; 134880 total
pages needed*
*DETAIL: Allocated FSM size: 1000 relations + 2 pages = 215 kB
shared memory.*
*WARNING: some databases have not been vacuumed in over 2 billion
transactions*
*DETAIL: You may have already suffered transaction-wraparound data loss.*
*
*

Is this an error happened between the vacuum? If so what can be done
next to prevent data loss? The vacuum was not done as superuser, we are
doing a second time vacuum as superuser now. And what are the further
steps to be followed now like reindexing,etc?

1. Did you take a full file-level backup of things before vacuuming?

2. What version?

3. How far back in the logs do the warnings go (you should have been
receiving warnings for a long time)?

4. How/why had you disabled/altered the autovacuum daemon?

This shouldn't really be possible without disabling autovaccuum or
configuring it strangely.

http://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

--
Richard Huxton
Archonet Ltd

--
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] Autodocumenting plpgsql function

2013-11-26 Thread Albe Laurenz

Rémi Cura wrote:
> somebody knows of a way to autodocument plpgsql function, in a docxygen style
> (adding tags in comments for instance, or creating doc templates to fill).
> 
> It would really help to write the doc and maintain it.

I am not sure what you need, but I see two ways to
document a function:

1) With /** .. */ comments in the beginning.
   Maybe doxygen can be used to parse a database dump.

2) With COMMENT ON FUNCTION ... IS '...';
   That also keeps the documentation close to where
   the code is, and it shows up in database dumps.

Yours,
Laurenz Albe

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

[GENERAL] a PostgreSQL slogan misused

2013-11-26 Thread Karsten Hilbert

I suppose the PostgreSQL Publice Relations people might be
interested in this misuse of PostgreSQL phrase:


http://www.computerweekly.com/blogs/open-source-insider/2013/11/why-elephants-never-forget-big-data.html

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Autodocumenting plpgsql function

2013-11-26 Thread Rémi Cura

Hey thanks for the answer.

I'm thinking way more than that.
Typically in you comments you include special tags, like @input, then
doxygen will parse it and generate an html documentation.

Cheers,

Rémi-C


2013/11/26 Albe Laurenz 

> Rémi Cura wrote:
> > somebody knows of a way to autodocument plpgsql function, in a docxygen
> style
> > (adding tags in comments for instance, or creating doc templates to
> fill).
> >
> > It would really help to write the doc and maintain it.
>
> I am not sure what you need, but I see two ways to
> document a function:
>
> 1) With /** .. */ comments in the beginning.
>Maybe doxygen can be used to parse a database dump.
>
> 2) With COMMENT ON FUNCTION ... IS '...';
>That also keeps the documentation close to where
>the code is, and it shows up in database dumps.
>
> Yours,
> Laurenz Albe
>

[GENERAL] xmlagg doesn't honor LIMIT?

2013-11-26 Thread Peter Kroon

Is anyone able to reproduce?
When I run the query below all 5 rows are returned instead of 2.
Or is this the default behaviour..

"PostgreSQL 9.2.4 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-52), 32-bit"

DROP TABLE IF EXISTS __pg_test_table CASCADE;
CREATE TABLE __pg_test_table(
id serial,
some_value int,
__rel int
);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(1,5);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(2,5);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(3,5);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(4,5);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(5,5);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(6,6);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(7,6);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(8,6);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(9,6);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(10,6);


SELECT
xmlagg(
xmlconcat(
xmlelement(name test_element,
xmlattributes(
0 AS m
),
xmlforest(
 dh.id AS i
,dh.some_value AS sv
)--xmlforest
)--test_element
)--xmlconcat
ORDER BY id DESC
)--xmlagg
FROM __pg_test_table AS dh
WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;

Re: [GENERAL] Autodocumenting plpgsql function

2013-11-26 Thread Albe Laurenz

Rémi Cura wrote:
>>> somebody knows of a way to autodocument plpgsql function, in a docxygen 
>>> style
>>> (adding tags in comments for instance, or creating doc templates to fill).
>>>
>>> It would really help to write the doc and maintain it.

> Typically in you comments you include special tags, like @input, then doxygen 
> will parse it and
> generate an html documentation.

I can't think of a way to automatically generate such
documentation from within PostgreSQL, except maybe that
you write a stored procedure that analyzes all functions
and adds a comment with the result.

Yours,
Laurenz Albe

-- 
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] xmlagg doesn't honor LIMIT?

2013-11-26 Thread Albe Laurenz

Peter Kroon wrote:
> Is anyone able to reproduce?
> When I run the query below all 5 rows are returned instead of 2.
> Or is this the default behaviour..

> SELECT
> xmlagg(
[...]
> )--xmlagg
> FROM __pg_test_table AS dh
> WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;

According to the documentation, that query should return
exactly one row since xmlagg is an aggregate.

So the LIMIT 2 won't do anything to the result.

You can wrap your query in a
SELECT count(*) FROM (SELECT ...) AS dummy;
to see how many rows you got.

Yours,
Laurenz Albe

-- 
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] Inserting rows containing composite foreign keys

2013-11-26 Thread Nelson Green

> To: pgsql-general@postgresql.org
> Date: Tue, 26 Nov 2013 08:15:45 +0100
> 
> Nelson Green, 25.11.2013 23:01:
> > Hello,
> > When inserting a record into the jobs table that references projects by 
> > name, do I have to query the projects table twice, 
> > once to get the funding source number, and once to get the project sequence 
> > number, even though both results will 
> > return the same row? Or put another way, is there a way to insert a row 
> > into the jobs table without having to 
> > perform two sub-queries for the same row, thus avoiding this:
> > 
> > INSERT INTO jobs
> >VALUES ((SELECT fundsrc_number FROM projects
> > WHERE project_name = 'proj1-1'),
> >(SELECT project_seq FROM projects
> > WHERE project_name = 'proj1-1'),
> > 1, 'job1-1.1', 'first project 1-1 job');
> > 
> 
> Use an INSERT based on a SELECT, not based on VALUES:
> 
>   INSERT INTO projects (fundsrc_number, project_seq, project_name, 
> project_desc)
>   SELECT fundsrc_number, 1, 'proj1-1', 'first source01 project'
>   FROM fundsrc
>   WHERE fundsrc_name IN ('source01', 'source02');
> 
>   INSERT INTO jobs (fundsrc_number, project_seq, job_seq, job_name, job_desc)
>   SELECT fundsrc_number, project_seq, 1, 'job1-1.1', 'first project 1-1 job'
>   FROM projects
>   WHERE project_name = 'proj1-1';

This works perfectly. I could swear I tried something similar and was 
unsuccessful, but I can't find any indication that I did in my history or my 
notes. Regardless, if you heard a loud smacking noise a few seconds ago that 
was my palm hitting my forehead. I appreciate you taking the time to state the 
obvious to the obviously blind.

> Note that it's good coding style to always specify the columns in an INSERT 
> statement. 
> It makes your statements more robust against changes.

I do. I just saved a few keystrokes and a bit of reading for this simple 
example. I know to specify and qualify at all times.

Thanks for both tips!

Re: [GENERAL] xmlagg doesn't honor LIMIT?

2013-11-26 Thread Peter Kroon

This is how I solved it:

SELECT
xmlagg(
xmlconcat(
xmlelement(name test_element,
xmlforest(
 ff.d AS a
)--xmlforest
)
)--xmlconcat
)--xmlagg

FROM (
SELECT
--xmlagg(
xmlconcat(
xmlelement(name test_element,
xmlattributes(
0 AS m
),
xmlforest(
 dh.id AS i
,dh.some_value AS sv
)--xmlforest
)--test_element
) AS d
--)
FROM __pg_test_table AS dh
WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;
) AS ff;


2013/11/26 Albe Laurenz 

> Peter Kroon wrote:
> > Is anyone able to reproduce?
> > When I run the query below all 5 rows are returned instead of 2.
> > Or is this the default behaviour..
>
> > SELECT
> > xmlagg(
> [...]
> > )--xmlagg
> > FROM __pg_test_table AS dh
> > WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;
>
> According to the documentation, that query should return
> exactly one row since xmlagg is an aggregate.
>
> So the LIMIT 2 won't do anything to the result.
>
> You can wrap your query in a
> SELECT count(*) FROM (SELECT ...) AS dummy;
> to see how many rows you got.
>
> Yours,
> Laurenz Albe
>

[GENERAL] restore crashes PG on Linux, works on Windows

2013-11-26 Thread Chris Curvey

I'm working with a vendor who is in the process of converting their system
from "something else" to Postgres.  Yay!

My vendor took a dump of our "something else" database (which runs on
Windows), did their conversion to Postgres, and then sent me back a
postgres dump (custom format) of the database for me to load onto my
servers for testing.

I was interested to find that while I can load the dump onto a PG 9.3
server running on Windows, I'm unable to load it on either 9.2 or 9.3
running on Linux.  At some point during the restore process (and it's not a
consistent point), PG on Linux crashes.

I suspect that the problem is related to the encoding specified in the
database dump:

CREATE DATABASE "TestDatabase" WITH TEMPLATE = template0 ENCODING = 'UTF8'
LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United
States.1252';

So my questions for the brain trust are:

1) Would you expect this to work?
2) If I had to load this database on Linux, what would be the best way to
go about it?  (see if I can find that charset/encoding for Linux?  Ask the
vendor for a plain-text dump? )

Re: [GENERAL] restore crashes PG on Linux, works on Windows

2013-11-26 Thread Andrew Sullivan

On Tue, Nov 26, 2013 at 09:25:17AM -0500, Chris Curvey wrote:
> 
> CREATE DATABASE "TestDatabase" WITH TEMPLATE = template0 ENCODING = 'UTF8'
> LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United
> States.1252';

Guess guessing, but I bet the collation is what hurts, just because
that collation causes problems generally.  Maybe you could get them to
ditch that in favour of something else?  LC_COLLATE = en_US.UTF-8
would perhaps be better.  

(The background for my guess: on your Linux box UTF-8 is likely the
normal local encoding, but on Windows that isn't true, and 1252 is
_almost_ but not quite Unicode.  This bites people generally in
internationalization.)

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
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] restore crashes PG on Linux, works on Windows

2013-11-26 Thread Albe Laurenz

Chris Curvey wrote:
> My vendor took a dump of our "something else" database (which runs on 
> Windows), did their conversion
> to Postgres, and then sent me back a postgres dump (custom format) of the 
> database for me to load onto
> my servers for testing.
> 
> 
> I was interested to find that while I can load the dump onto a PG 9.3 server 
> running on Windows, I'm
> unable to load it on either 9.2 or 9.3 running on Linux.  At some point 
> during the restore process
> (and it's not a consistent point), PG on Linux crashes.

You mean, the database server dies?
Or that there is an error message?
If it is the latter, can we see the error message?

> I suspect that the problem is related to the encoding specified in the 
> database dump:
> 
> CREATE DATABASE "TestDatabase" WITH TEMPLATE = template0 ENCODING = 'UTF8' 
> LC_COLLATE =
> 'English_United States.1252' LC_CTYPE = 'English_United States.1252';

Yes, that should throw an error on a Linux system.
But you should get that error consistently, different from
what you write above.

> So my questions for the brain trust are:
> 
> 
> 1) Would you expect this to work?

No, as stated above.

> 2) If I had to load this database on Linux, what would be the best way to go 
> about it?  (see if I can
> find that charset/encoding for Linux?  Ask the vendor for a plain-text dump? )

You can create the database beforehand and ignore the one error
from pg_restore.

You can convert the custom format dump into an SQL file with
pg_restore -f dumpfile.sql dumpfile.dmp

Yours,
Laurenz Albe

-- 
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] restore crashes PG on Linux, works on Windows

2013-11-26 Thread Albe Laurenz

Andrew Sullivan wrote:
> Guess guessing, but I bet the collation is what hurts, [...]

> (The background for my guess: on your Linux box UTF-8 is likely the
> normal local encoding, but on Windows that isn't true, and 1252 is
> _almost_ but not quite Unicode.  This bites people generally in
> internationalization.)

I beg your pardon, but Windows-1252 has nothing to do with Unicode
or UTF-8.  The only connection is that Windows-1252 and UTF-8 both
are ASCII supersets.

Yours,
Laurenz Albe

-- 
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] restore crashes PG on Linux, works on Windows

2013-11-26 Thread Andrew Sullivan

On Tue, Nov 26, 2013 at 02:48:34PM +, Albe Laurenz wrote:
> I beg your pardon, but Windows-1252 has nothing to do with Unicode

Sorry, you're quite right, I'm having a brain fade (I meant ISO
8859-1, of course).

The point I wanted to make, however, is that the collation often
causes trouble with UTF-8 encoding.  I liked your conversion
suggestion, however, in your other mail.

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


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

[GENERAL] tracking scripts...

2013-11-26 Thread Joey Quinn

I have a fairly large table (4.3 billion rows) that I am running an update
script on (a bit over 127 thousand individual update queries). I am using
the gui. It has been running for about 24 hours now. Is there any good way
to gauge progress (as in, how many of the individual update queries have
finished)?

53 matches

Mail list logo