[GENERAL] vacuumdb --analyze-only scans all pages?

[Gerhard Wiesinger]

Hello,

PostgreSQl  9.6.1: after a pg_dump/restore procedure it scans all pages 
(at least for some of the tables, analyze-only switch is specified).


I would expect that only the sample rows are scanned.

"log_details": scanned 2133350 of 2133350 pages

vacuumdb --analyze-only --all --verbose
INFO:  analyzing "public.log"
INFO:  "log": scanned 3 of 30851 pages, containing 3599899 live rows 
and 0 dead rows; 3 rows in sample, 3702016 estimated total rows

INFO:  analyzing "public.log_details"
INFO:  "log_details": scanned 2133350 of 2133350 pages, containing 
334935843 live rows and 0 dead rows; 300 rows in sample, 334935843 
estimated total rows

INFO:  analyzing "public.log_details_str"
INFO:  "log_details_str": scanned 3 of 521126 pages, containing 
3601451 live rows and 0 dead rows; 3 rows in sample, 62560215 
estimated total rows


Any ideas why?

Thnx.

Ciao,

Gerhard



--
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] Help with Trigger

[Clifford Snow]

Thank you for your suggestion which solved the problem. Much better
solution that what I was trying to accomplish. Much smaller table to query
since it only has one entry per user.

Clifford

On Wed, Dec 28, 2016 at 8:12 PM, Adrian Klaver 
wrote:

> On 12/28/2016 07:06 PM, Clifford Snow wrote:
>
>> I'm trying to write a trigger (my first) to update another table if the
>> user_id is new. But I'm getting a index exception that the user_id
>>
>
> What is the actual error message?
>
> already exists. I'm picking up data from another feed which gives
>> provides me with changes to the main database.
>>
>> what I have is
>>
>> CREATE OR REPLACE FUNCTION add_new_user()
>> RETURNS TRIGGER AS
>> $BODY$
>> DECLARE
>> commits RECORD;
>> BEGIN
>> SELECT INTO commits * FROM changes WHERE user_id = NEW.user_id;
>>
>
> In the above you are checking whether the changes table has the user_id
> and if does not then creating a new user in the user table below. Not sure
> how they are related, but from the description of the error it would seem
> they are not that tightly coupled. In other words just because the user_id
> does not exist in changes does not ensure it also absent from the table
> user. Off the top of head I would say the below might be a better query:
>
> SELECT INTO commits * FROM user WHERE user_id = NEW.user_id;
>
> Though it would help the debugging process if you showed the complete
> schema for both the changes and user tables.
>
>
> IF NOT FOUND
>> THEN
>> INSERT INTO user (user_name, user_id, change_id,
>> created_date)
>> VALUES(NEW.user_name, NEW.user_id,
>> NEW.change_id, NEW.created_date);
>> END IF;
>> RETURN NEW;
>> END;
>> $BODY$
>> LANGUAGE plpgsql;
>>
>> CREATE TRIGGER add_new_user_trigger
>> BEFORE INSERT ON changes
>> FOR EACH ROW
>> EXECUTE PROCEDURE add_new_user();
>>
>> I hoping for some recommendations on how to fix or at where I'm going
>> wrong.
>>
>> Thanks,
>> Clifford
>>
>>
>> --
>> @osm_seattle
>> osm_seattle.snowandsnow.us 
>> OpenStreetMap: Maps with a human touch
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch

Re: [GENERAL] Help with Trigger

[Adrian Klaver]

On 12/28/2016 07:06 PM, Clifford Snow wrote:

I'm trying to write a trigger (my first) to update another table if the
user_id is new. But I'm getting a index exception that the user_id


What is the actual error message?


already exists. I'm picking up data from another feed which gives
provides me with changes to the main database.

what I have is

CREATE OR REPLACE FUNCTION add_new_user()
RETURNS TRIGGER AS
$BODY$
DECLARE
commits RECORD;
BEGIN
SELECT INTO commits * FROM changes WHERE user_id = NEW.user_id;


In the above you are checking whether the changes table has the user_id 
and if does not then creating a new user in the user table below. Not 
sure how they are related, but from the description of the error it 
would seem they are not that tightly coupled. In other words just 
because the user_id does not exist in changes does not ensure it also 
absent from the table user. Off the top of head I would say the below 
might be a better query:


SELECT INTO commits * FROM user WHERE user_id = NEW.user_id;

Though it would help the debugging process if you showed the complete 
schema for both the changes and user tables.




IF NOT FOUND
THEN
INSERT INTO user (user_name, user_id, change_id,
created_date)
VALUES(NEW.user_name, NEW.user_id,
NEW.change_id, NEW.created_date);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER add_new_user_trigger
BEFORE INSERT ON changes
FOR EACH ROW
EXECUTE PROCEDURE add_new_user();

I hoping for some recommendations on how to fix or at where I'm going wrong.

Thanks,
Clifford


--
@osm_seattle
osm_seattle.snowandsnow.us 
OpenStreetMap: Maps with a human touch



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


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

[GENERAL] Help with Trigger

[Clifford Snow]

I'm trying to write a trigger (my first) to update another table if the
user_id is new. But I'm getting a index exception that the user_id already
exists. I'm picking up data from another feed which gives provides me with
changes to the main database.

what I have is

CREATE OR REPLACE FUNCTION add_new_user()
RETURNS TRIGGER AS
$BODY$
DECLARE
commits RECORD;
BEGIN
SELECT INTO commits * FROM changes WHERE user_id = NEW.user_id;
IF NOT FOUND
THEN
INSERT INTO user (user_name, user_id, change_id,
created_date)
VALUES(NEW.user_name, NEW.user_id, NEW.change_id,
NEW.created_date);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER add_new_user_trigger
BEFORE INSERT ON changes
FOR EACH ROW
EXECUTE PROCEDURE add_new_user();

I hoping for some recommendations on how to fix or at where I'm going wrong.

Thanks,
Clifford


-- 
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch

Re: [GENERAL] Securing Information

[jesusthefrog]

True. In our environment we have other layers to deal with network security
which covers us for encryption of a data as it's transmitted, and you
definitely should do that, but that's not something a DBA would normally be
concerned with.
And given that he said that the machine may be standalone, I would suspect
that there would be a person at a directly (or nearly-directly) connected
terminal, possibly via a web app. If you're going to, for example, email
the data to someone, then it also has to be encrypted at that time, but
that would need to be re-encrypted with a method the receiver would be able
to decrypt anyway.

In any case I recommend reading the consolidated HIPAA Privacy Act
regulations (
https://www.hhs.gov/sites/default/files/ocr/privacy/hipaa/administrative/combined/hipaa-simplification-201303.pdf
).

I'm not entirely sure of the applicability of the Privacy Act of 1974 to
non-government-affiliated computers/networks, but it can't hurt to read
through that regulation for guidance as well. (
https://gsa.gov/portal/mediaId/252231/fileName/CIO_P_21001I__CHGE_1_GSA_Information_Technology_%28IT%29_Security_Policy__%28Signed_on_10-20-2015%29.action
page 35).

-- 
-BEGIN GEEK CODE BLOCK-
  Version: 3.12
  GIT d- s+ a- C L+++ S++ B+ P++> E++ W+++
  N o? K- !w O- M- V? PS++ PE- Y+ PGP t+ 5+++
  X R+ tv b+++ DI++ D++ G+ e-- h- r++ y
--END GEEK CODE BLOCK--

Re: [GENERAL] Securing Information

[John R Pierce]

On 12/28/2016 4:16 PM, jesusthefrog wrote:


If you're storing HIPAA data and/or PII then just make sure it's 
encrypted at rest. We just did this at my workplace by using full disk 
encryption on the disk which stores the DB files.

That may not be the best solution, but it appears to work well enough.



data really should be encrypted at the end point it originates and only 
decrypted at the end point where its used.yes, this presents all 
sorts of annoying issues for everything in between, but anything less is 
false security.


the problem with full disk encryption, as long as the volume is mounted, 
the data is visible as the encryption keys are loaded at boot or mount 
time.  the only threat model FDE protects against is physical theft of 
the server.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Securing Information

[jesusthefrog]

If you're storing HIPAA data and/or PII then just make sure it's encrypted
at rest. We just did this at my workplace by using full disk encryption on
the disk which stores the DB files.
That may not be the best solution, but it appears to work well enough.

-- 
-BEGIN GEEK CODE BLOCK-
  Version: 3.12
  GIT d- s+ a- C L+++ S++ B+ P++> E++ W+++
  N o? K- !w O- M- V? PS++ PE- Y+ PGP t+ 5+++
  X R+ tv b+++ DI++ D++ G+ e-- h- r++ y
--END GEEK CODE BLOCK--

Re: [GENERAL] Er Data Modeller for PostgreSQL

[Rich Shepard]

On Wed, 28 Dec 2016, Adrian Klaver wrote:


An example from my machine that works:
aklaver@tito:~/bin> java -jar schemaSpy_5.0.0.jar -t pgsql -s public -u 
postgres  -db production -host localhost  -dp 
/home/aklaver/bin/postgresql-9.4.1212.jre6.jar -o s_spy


Adrian,

  That's interesting. I specified my username, not postgres, since that's
how I access the databases from the psql CLI. But, since I've resolved the
issue to my satisfaction and deleted the schemaSpy subdirectory I've no
incentive to try again.

  Time now to fill them thar tables with fake data so I can test as I
develop.

Happy new year to you,

Rich


--
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] Er Data Modeller for PostgreSQL

[Adrian Klaver]

On 12/28/2016 03:24 PM, Rich Shepard wrote:

On Fri, 23 Dec 2016, Thomas Kellerer wrote:


Scott Mead just blogged about using SchemaSpy with Postgres


Thomas,

  I've spent the past two days without success trying to get schemaSpy
running here


An example from my machine that works:

aklaver@tito:~/bin> java -jar schemaSpy_5.0.0.jar -t pgsql -s public -u 
postgres  -db production -host localhost  -dp 
/home/aklaver/bin/postgresql-9.4.1212.jre6.jar -o s_spy


Using database properties:
  [schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/pgsql.properties
Gathering schema 
details(2sec)

Writing/graphing summary..(3sec)
Writing/diagramming 
details.(14sec)
Wrote relationship details of 69 tables/views to directory 's_spy' in 20 
seconds.

View the results by opening s_spy/index.html



and have found a solution that works quickly and easily: dbeaver
. Yes, it's a GUI rather than CLI application but
it works ... and it's F/OSS under the GPL. Best of all worlds.


Looks interesting.



  I downloaded the postgresql-jdbc driver and installed it in a couple of
places trying to make schemaSpy happy, and futzed with pg_hba.conf access
control. The dbeaver application downloaded and internally installed the
postgresql-jdbc driver it wants to use.

  It took no time to figure out how to create, test, and establish a
connection to the databases and examine their E-R diagrams. They're now
saved as .png files.

Thanks very much for your pointer and my wishes for a healthy, happy, and
prosperous 2017 to you and all who responded to my question,

Rich







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


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

Re: [GENERAL] Er Data Modeller for PostgreSQL

[Rich Shepard]

On Fri, 23 Dec 2016, Thomas Kellerer wrote:


Scott Mead just blogged about using SchemaSpy with Postgres


Thomas,

  I've spent the past two days without success trying to get schemaSpy running 
here
and have found a solution that works quickly and easily: dbeaver
. Yes, it's a GUI rather than CLI application but
it works ... and it's F/OSS under the GPL. Best of all worlds.

  I downloaded the postgresql-jdbc driver and installed it in a couple of
places trying to make schemaSpy happy, and futzed with pg_hba.conf access
control. The dbeaver application downloaded and internally installed the
postgresql-jdbc driver it wants to use.

  It took no time to figure out how to create, test, and establish a
connection to the databases and examine their E-R diagrams. They're now
saved as .png files.

Thanks very much for your pointer and my wishes for a healthy, happy, and
prosperous 2017 to you and all who responded to my question,

Rich




--
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] Securing Information

[Adrian Klaver]

On 12/27/2016 11:59 AM, Chris Weekes wrote:

Hello Sir or Madam:

I wanted to install PostgreSQL v 9.6 on a machine that may
stand alone and or be part of a network sharing hospital
data.

I was wondering what steps if any need to be taken to ensure
that the patient and operational data is secure on a machine
and or across the network.


That is a large question that depends to a great deal on what the rules 
say for whatever legal jurisdictions you are working in. Assuming you do 
not like spending time in court.




Thank you,
Chris



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


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

Re: [GENERAL] Performance PLV8 vs PLPGSQL

[Francisco Olarte]

On Wed, Dec 28, 2016 at 5:53 PM, Jan de Visser  wrote:
> .but the term "impedance mismatch"
> is at least 25 year old;

Much older, I was told it in class at least 32 years ago.

> as far as I know it was coined

_Borrowed_ from electrical engineering / communication techs.

It is used to highlight how signals 'bounce' at the points of a
transmision path where impedances do not match. It extrapolates the
fact that if you have a battery with an internal resistance R the way
to extract the maximum energy on a load is for it to match the
impedance, be R too. Higher load impedance and the fraction of energy
in the load goes up, the total down. Lower load impedance and the
fraction in the load goes down, the total up. In either case absolute
power in the load goes down. Match the impedance and the energy in the
load is the maximum ( and equal to the internal loss in the battery ).

The term has been used in radio texts since the dawn of ( radio ) times.

It's used a lot as a similar problem appears when mixing to different
technology, each time you cross the barrier you loose something, or
hit a problem.

> And despite the smart people in academia warning us about that mismatch in the
> early 90s, we bravely soldiered (I'm taking full blame myself here) on and
> 10-15 years later came up with abominations like Hibernate...
> History lesson over, carry on...

I think that goes together with "everyone can be a programmer" and
"every Java ( a language with several apparent concessions made to
people who did not even understand objects, like String.format, and
targeted to enable "everyone" to do OO ) coder can do databases".

Well, rant mode off. Today is "dia de los Inocentes", spanish version
of April Fools I was tempted to write something about different
impedances in the copper tracks used for DB data traffic when entering
the CPU silicon interconnects via golden cables.


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] LYDB: What advice about stored procedures and other server side code?

[Karsten Hilbert]

> Many applications are not designed to have a "stable" database API.

It seems OP is arguing they should.

Regards,
Karsten


-- 
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] LYDB: What advice about stored procedures and other server side code?

[Christoph Moench-Tegeder]

## Mike Sofen (mso...@runbox.com):

> I look at this from the opposite direction: with a stable database
> API (via stored procs), I can change the schema and logic within the
> procs without causing any app code breakage…the app tier is completely
> insulated from those changes – that’s worth a lot.

Many applications are not designed to have a "stable" database API.
And suddenly... there's not only dependencies between functions
in the "core application" but also between application and database,
in more ways than "just a few tables". One needs to be aware of
that.

> Our dedicated db servers have not yet shown any real CPU consumption
> during app use - memory, for us, is the only real limiting factor.

That depends on the usage pattern. An application for "company internal"
use will behave quite differently from a popular internet application.
I do know of cases where too much code in the database became the
number one problem.
What I'm saying is: choose wisely, and be prepared for the day where
all your assumptions will be proven wrong. Predicting future use and
changes of an application in an emergent field is near impossible.
Database-side code can be a great thing, and a real problem. In the
worst case, it's both at the same time.

Regards,
Christoph

-- 
Spare Space


-- 
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] Securing Information

[Melvin Davidson]

On Wed, Dec 28, 2016 at 11:49 AM, Rich Shepard 
wrote:

> On Tue, 27 Dec 2016, Chris Weekes wrote:
>
> I was wondering what steps if any need to be taken to ensure that the
>> patient and operational data is secure on a machine and or across the
>> network.
>>
>
> Chris,
>
>   I'm far from an expert but until more knowledgeable folks respond I'll
> offer a couple of quick suggestins. First, in your postgres data/ directory
> modify pg_hba.conf. Specifying hostssl and an appropriate authentication
> method
> will help. Second, partition your users into postgres role specifying what
> each role can access and do.
>
>   The postgresql-9.6-US.pdf manual has all the details.
>
> HTH,
>
> Rich
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>




*Another thing to consider is DO NOT create your tables in the public
schema.Instead, create a separate schema, then only grant access to that
schema and tablesto users that are authorized to access them.*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Row value expression much faster than equivalent OR clauses

[Steven Grimm]

A library my application is using does a "scan a batch at a time" loop 
over a table of events, keeping track of its last position so it can 
start the next query in the right place.


SELECT eventIdentifier, aggregateIdentifier, sequenceNumber, timeStamp, 
payloadType,

payloadRevision, payload, metaData
FROM DomainEventEntry e
WHERE ((e.timeStamp > '2016-12-19T20:34:22.315Z')
OR (e.timeStamp = '2016-12-19T20:34:22.315Z'
AND e.sequenceNumber > 0)
OR (e.timeStamp = '2016-12-19T20:34:22.315Z'
AND e.sequenceNumber = 0
AND e.aggregateIdentifier > 
'dev:642e1953-2562-4768-80d9-0c3af9b0ff84'))

AND (type = 'transAggPrototype')
ORDER BY e.timeStamp ASC, e.sequenceNumber ASC, e.aggregateIdentifier ASC
LIMIT 100;

This uses the index on the three columns it's using for ordering of 
events, but (if I'm reading the explain output correctly) does a full 
scan of the index.


 Limit  (cost=0.55..1349.44 rows=100 width=576) (actual 
time=526.814..527.238 rows=100 loops=1)
   ->  Index Scan using domainevententry_ts_seq_agg on domainevententry 
e  (cost=0.55..92494.44 rows=6857 width=576) (actual 
time=526.811..527.035 rows=100 loops=1)
 Filter: (((type)::text = 'transAggPrototype'::text) AND 
((("timestamp")::text > '2016-12-19T20:34:22.315Z'::text) OR 
((("timestamp")::text = '2016-12-19T20:34:22.315Z'::text) AND 
(sequencenumber > 0)) OR ((("timestamp")::text = 
'2016-12-19T20:34:22.315Z'::text) AND (sequencenumber = 0) AND 
((aggregateidentifier)::text > 
'dev:642e1953-2562-4768-80d9-0c3af9b0ff84'::text

 Rows Removed by Filter: 332183
 Planning time: 1.893 ms
 Execution time: 527.368 ms


I played around with it a little and one thing I tried was to 
restructure the WHERE clause using a row value expression that's 
semantically equivalent to the original.


SELECT eventIdentifier, aggregateIdentifier, sequenceNumber, timeStamp, 
payloadType,

payloadRevision, payload, metaData
FROM DomainEventEntry e
WHERE (e.timeStamp, e.sequenceNumber, e.aggregateIdentifier) >
('2016-11-19T20:34:22.315Z', 0, 
'dev:642e1953-2562-4768-80d9-0c3af9b0ff84')

AND (type = 'transAggPrototype')
ORDER BY e.timeStamp ASC, e.sequenceNumber ASC, e.aggregateIdentifier ASC
LIMIT 100;

This ends up being a LOT faster:

 Limit  (cost=0.55..56.81 rows=100 width=576) (actual time=0.065..0.667 
rows=100 loops=1)
   ->  Index Scan using domainevententry_ts_seq_agg on domainevententry 
e  (cost=0.55..65581.93 rows=116573 width=576) (actual time=0.062..0.437 
rows=100 loops=1)
 Index Cond: (ROW(("timestamp")::text, sequencenumber, 
(aggregateidentifier)::text) > ROW('2016-11-19T20:34:22.315Z'::text, 0, 
'dev:642e1953-2562-4768-80d9-0c3af9b0ff84'::text))

 Filter: ((type)::text = 'transAggPrototype'::text)
 Rows Removed by Filter: 235
 Planning time: 1.705 ms
 Execution time: 0.795 ms


I wonder if the query planner could recognize that the two queries are 
equivalent and choose the second plan for the OR-clause version, or at 
least use the index more efficiently. This is on PostgreSQL 9.5.2.


-Steve

Re: [GENERAL] Performance PLV8 vs PLPGSQL

[Jan de Visser]

On Wednesday, December 28, 2016 6:02:51 AM EST Mike Sofen wrote:
> the natural lashup of plpgsql to postgres (I liked Alban’s term,
> “impedance”), is a key aspect.

Not to deprive Alban of any of his credit, but the term "impedance mismatch" 
is at least 25 year old; as far as I know it was coined to describe the 
problems arising from attempting to shoehorn an OO model onto a relational 
database.

And despite the smart people in academia warning us about that mismatch in the 
early 90s, we bravely soldiered (I'm taking full blame myself here) on and 
10-15 years later came up with abominations like Hibernate...

History lesson over, carry on...




-- 
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] Securing Information

[Rich Shepard]

On Tue, 27 Dec 2016, Chris Weekes wrote:


I was wondering what steps if any need to be taken to ensure that the
patient and operational data is secure on a machine and or across the
network.


Chris,

  I'm far from an expert but until more knowledgeable folks respond I'll
offer a couple of quick suggestins. First, in your postgres data/ directory
modify pg_hba.conf. Specifying hostssl and an appropriate authentication method
will help. Second, partition your users into postgres role specifying what
each role can access and do.

  The postgresql-9.6-US.pdf manual has all the details.

HTH,

Rich


--
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] Indexes and loops

[Pavel Stehule]

Hi

2016-12-27 19:05 GMT+01:00 Арсен Арутюнян :

> Hello.
>
> I have a few questions:
>
> 1)  JobStatusTest1 function has only one request and JobStatusTest2
> function has as many as six requests.
>
> Why function JobStatusTest2 is faster?
>
>
> JobStatusTest1 : 981.596 ms
>
> JobStatusTest2 : 849.133 ms
>
>
> 2) Two functions JobStatusTest3 and JobStatusTest4 perform the same
> actions. But one of the function performs the same steps in the cycle.
>
> Why is the speed of the functions so incredibly much different?
>
>
> JobStatusTest3 : 1430.777 ms
>
> JobStatusTest4 : 2.386 ms
>
>
> best
> Arsen Arutyunyan
> 
> =
>
> CREATE TABLE test_job(id serial,primary key(id));
> insert into test_job (id) values (1);
> CREATE TABLE test_status(id serial,primary key(id));
> insert into test_status (id) values (1),(2),(4),(8),(16),(32);
> CREATE TABLE test_task(id serial,job_id integer references test_job on
> delete cascade,status_id integer references test_status on delete
> cascade,primary key(id));
> CREATE INDEX CONCURRENTLY test_job_idx on test_job(id);
> CREATE INDEX CONCURRENTLY test_status_idx on test_status(id);
> CREATE INDEX CONCURRENTLY test_task_idx on test_task(id);
> CREATE INDEX CONCURRENTLY test_task_idx_cnt1 on test_task(id,job_id) where
> status_id=1;
> CREATE INDEX CONCURRENTLY test_task_idx_cnt2 on test_task(id,job_id) where
> status_id=2;
> CREATE INDEX CONCURRENTLY test_task_idx_cnt3 on test_task(id,job_id) where
> status_id=4;
> CREATE INDEX CONCURRENTLY test_task_idx_cnt4 on test_task(id,job_id) where
> status_id=8;
> CREATE INDEX CONCURRENTLY test_task_idx_cnt5 on test_task(id,job_id) where
> status_id=16;
> CREATE INDEX CONCURRENTLY test_task_idx_cnt6 on test_task(id,job_id) where
> status_id=32;
>
>
> insert into test_task (id,job_id,status_id) values
> (generate_series(1,10,1),1,1);
> insert into test_task (id,job_id,status_id) values (generate_series(11,
> 60,1),1,2);
> insert into test_task (id,job_id,status_id) values (generate_series(61,
> 100,1),1,4);
> insert into test_task (id,job_id,status_id) values
> (generate_series(101,170,1),1,8);
> insert into test_task (id,job_id,status_id) values
> (generate_series(171,250,1),1,16);
> insert into test_task (id,job_id,status_id) values
> (generate_series(251,300,1),1,32);
>
>
> CREATE OR REPLACE FUNCTION JobStatusTest1(JobID integer) RETURNS void AS $$
> DECLARE
> CurrentQuery RECORD;
> BEGIN
> FOR CurrentQuery IN select count(test_task.id) as counter, status_id from
> test_job inner join test_task on (test_job.id=test_task.job_id) where
> test_job.id=JobID group by status_id LOOP
> raise notice 'Conter:% Status:%', CurrentQuery.counter,
> CurrentQuery.status_id;
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
>
> explain analyze select * from JobStatusTest1(1);
>
> CREATE OR REPLACE FUNCTION JobStatusTest2(JobID integer) RETURNS void AS $$
> DECLARE
> CurrentQuery RECORD;
> counter integer;
> BEGIN
> select count(test_task.id) into counter from test_job inner join
> test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and
> test_task.status_id=1;
> raise notice 'Conter:% Status:%', counter, 1;
> select count(test_task.id) into counter from test_job inner join
> test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and
> test_task.status_id=2;
> raise notice 'Conter:% Status:%', counter, 2;
> select count(test_task.id) into counter from test_job inner join
> test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and
> test_task.status_id=4;
> raise notice 'Conter:% Status:%', counter, 4;
> select count(test_task.id) into counter from test_job inner join
> test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and
> test_task.status_id=8;
> raise notice 'Conter:% Status:%', counter, 8;
> select count(test_task.id) into counter from test_job inner join
> test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and
> test_task.status_id=16;
> raise notice 'Conter:% Status:%', counter, 16;
> select count(test_task.id) into counter from test_job inner join
> test_task on (test_job.id=test_task.job_id) where test_job.id=JobID and
> test_task.status_id=32;
> raise notice 'Conter:% Status:%', counter, 32;
> END;
> $$ LANGUAGE plpgsql;
>
> explain analyze select * from JobStatusTest2(1);
>
> CREATE OR REPLACE FUNCTION JobStatusTest3(JobID integer) RETURNS void AS $$
> DECLARE
> CurrentQuery RECORD;
> taskid integer;
> BEGIN
> FOR CurrentQuery IN select id from test_status LOOP
> SELECT test_task.id into taskid from test_job inner join test_task on (
> test_job.id=test_task.job_id) where test_job.id=JobID and
> test_task.status_id=CurrentQuery.id ORDER BY test_task.id limit 1;
> raise notice 'TaskID:% Status:%', taskid, CurrentQuery.id;
> END LOOP;
> END;
> $$ LANGUAGE plpgsql;
>
> explain analyze select * from JobStatusTest3(1);
>
> CREATE OR 

[GENERAL] Securing Information

[Chris Weekes]

Hello Sir or Madam:

I wanted to install PostgreSQL v 9.6 on a machine that may
stand alone and or be part of a network sharing hospital
data.

I was wondering what steps if any need to be taken to ensure
that the patient and operational data is secure on a machine
and or across the network.

Thank you,
Chris

[GENERAL] Indexes and loops

[Арсен Арутюнян]

Hello.
I have a few questions:
1)  JobStatusTest1 function has only one request and JobStatusTest2 function 
has as many as six requests.
Why function JobStatusTest2 is faster? 

JobStatusTest1 : 981.596 ms
JobStatusTest2 : 849.133 ms

2) Two functions JobStatusTest3 and JobStatusTest4 perform the same actions. 
But one of the function performs the same steps in the cycle.
Why is the speed of the functions so incredibly much different?

JobStatusTest3 : 1430.777 ms
JobStatusTest4 : 2.386 ms

best
Arsen Arutyunyan
=

CREATE TABLE test_job(id serial,primary key(id));
insert into test_job (id) values (1);
CREATE TABLE test_status(id serial,primary key(id));
insert into test_status (id) values (1),(2),(4),(8),(16),(32);
CREATE TABLE test_task(id serial,job_id integer references test_job on delete 
cascade,status_id integer references test_status on delete cascade,primary 
key(id));
CREATE INDEX CONCURRENTLY test_job_idx on test_job(id);
CREATE INDEX CONCURRENTLY test_status_idx on test_status(id);
CREATE INDEX CONCURRENTLY test_task_idx on test_task(id);
CREATE INDEX CONCURRENTLY test_task_idx_cnt1 on test_task(id,job_id) where 
status_id=1;
CREATE INDEX CONCURRENTLY test_task_idx_cnt2 on test_task(id,job_id) where 
status_id=2;
CREATE INDEX CONCURRENTLY test_task_idx_cnt3 on test_task(id,job_id) where 
status_id=4;
CREATE INDEX CONCURRENTLY test_task_idx_cnt4 on test_task(id,job_id) where 
status_id=8;
CREATE INDEX CONCURRENTLY test_task_idx_cnt5 on test_task(id,job_id) where 
status_id=16;
CREATE INDEX CONCURRENTLY test_task_idx_cnt6 on test_task(id,job_id) where 
status_id=32;

insert into test_task (id,job_id,status_id) values 
(generate_series(1,10,1),1,1);
insert into test_task (id,job_id,status_id) values 
(generate_series(11,60,1),1,2);
insert into test_task (id,job_id,status_id) values 
(generate_series(61,100,1),1,4);
insert into test_task (id,job_id,status_id) values 
(generate_series(101,170,1),1,8);
insert into test_task (id,job_id,status_id) values 
(generate_series(171,250,1),1,16);
insert into test_task (id,job_id,status_id) values 
(generate_series(251,300,1),1,32);

CREATE OR REPLACE FUNCTION JobStatusTest1(JobID integer) RETURNS void AS $$
DECLARE
CurrentQuery RECORD;
BEGIN
FOR CurrentQuery IN select count(test_task.id) as counter, status_id from 
test_job inner join test_task on (test_job.id=test_task.job_id) where 
test_job.id=JobID group by status_id LOOP
raise notice 'Conter:% Status:%', CurrentQuery.counter, CurrentQuery.status_id;
END LOOP;
END;
$$ LANGUAGE plpgsql;
explain analyze select * from JobStatusTest1(1);
CREATE OR REPLACE FUNCTION JobStatusTest2(JobID integer) RETURNS void AS $$
DECLARE
CurrentQuery RECORD;
counter integer;
BEGIN
select count(test_task.id) into counter from test_job inner join test_task on 
(test_job.id=test_task.job_id) where test_job.id=JobID and 
test_task.status_id=1;
raise notice 'Conter:% Status:%', counter, 1;
select count(test_task.id) into counter from test_job inner join test_task on 
(test_job.id=test_task.job_id) where test_job.id=JobID and 
test_task.status_id=2;
raise notice 'Conter:% Status:%', counter, 2;
select count(test_task.id) into counter from test_job inner join test_task on 
(test_job.id=test_task.job_id) where test_job.id=JobID and 
test_task.status_id=4;
raise notice 'Conter:% Status:%', counter, 4;
select count(test_task.id) into counter from test_job inner join test_task on 
(test_job.id=test_task.job_id) where test_job.id=JobID and 
test_task.status_id=8;
raise notice 'Conter:% Status:%', counter, 8;
select count(test_task.id) into counter from test_job inner join test_task on 
(test_job.id=test_task.job_id) where test_job.id=JobID and 
test_task.status_id=16;
raise notice 'Conter:% Status:%', counter, 16;
select count(test_task.id) into counter from test_job inner join test_task on 
(test_job.id=test_task.job_id) where test_job.id=JobID and 
test_task.status_id=32;
raise notice 'Conter:% Status:%', counter, 32;
END;
$$ LANGUAGE plpgsql;
explain analyze select * from JobStatusTest2(1);
CREATE OR REPLACE FUNCTION JobStatusTest3(JobID integer) RETURNS void AS $$
DECLARE
CurrentQuery RECORD;
taskid integer;
BEGIN
FOR CurrentQuery IN select id from test_status LOOP
SELECT test_task.id into taskid from test_job inner join test_task on 
(test_job.id=test_task.job_id) where test_job.id=JobID and 
test_task.status_id=CurrentQuery.id ORDER BY test_task.id limit 1;
raise notice 'TaskID:% Status:%', taskid, CurrentQuery.id;
END LOOP;
END;
$$ LANGUAGE plpgsql;
explain analyze select * from JobStatusTest3(1);
CREATE OR REPLACE FUNCTION JobStatusTest4(JobID integer) RETURNS void AS $$
DECLARE
CurrentQuery RECORD;
taskid integer;
BEGIN
SELECT test_task.id into taskid from test_job inner join test_task on 
(test_job.id=test_task.job_id) where test_job.id=JobID and 
test_task.status_id=1 ORDER BY test_task.id limit 

Re: [GENERAL] LYDB: What advice about stored procedures and other server side code?

[Mike Sofen]

|From: Christoph Moench-Tegeder
|Initially, running code in your database can make life easier for the 
developers
|(ise pgTap for testing, pl/profiler and pl/debugger, etc.). But once you have 
to
|change your schema, the hurt begins:
|you'll need downtime for that, or you'll have to deal with the possibility of
|changing the API of your "database side" code, and matching code and tables on
|the database.

I look at this from the opposite direction: with a stable database API (via 
stored procs), I can change the schema and logic within the procs without 
causing any app code breakage…the app tier is completely insulated from those 
changes – that’s worth a lot.  Yes, for deploying the schema change there must 
be an outage, but that’s true regardless of where the data is being manipulated 
– the data is still in a db…and there are ways to mitigate/eliminate the 
duration of the outage.

|The next pain point is scalability: running code on the database server puts 
your
|code on the most expensive and hardest to scale CPUs. You can (almost) always
|add another appserver to your setup (just spin up a VM with a tomcat or
|whatever-you-use). But if the bottleneck is your database CPUs, you'd have to
|move to a larger server 

Our dedicated db servers have not yet shown any real CPU consumption during app 
use - memory, for us, is the only real limiting factor.  The only time CPU 
consumption spikes is during admin activities - reindexing, vacuuming, bulk 
data loads...that sort of thing.  Even the boxplot calculations barely cause a 
ripple.  To me that speaks to the efficiency of language and engine working 
together.  You are right of course on the scaling - if we do run out of CPU 
horsepower and cannot scale up any more, we'd have to scale out, and there are 
ways to do that too.  IOWs, the model doesn't have to change, just the solution 
to solve the scaling (solve the problem, not the symptom).

|TL;DR: database side code can be a great thing in a small application, but once
|the application and traffic grows, "code in the database"
|requires specialist attention and may become a burden.
|Unfortunately, most large applications started small...

In my opinion, having a database specialist work on database stuff is a GOOD 
thing.  Tables get designed properly, correct indexes are built, efficient 
query plans are created, etc.  ORMs are a shortcut to getting an app talking to 
data, but aren't a substitute for a proper, scalable data tier.  IMO...being a 
data specialist...  :-)

Mike Sofen (Synthetic Genomics)



-- 
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] LYDB: What advice about stored procedures and other server side code?

[Pavel Stehule]

2016-12-28 16:12 GMT+01:00 Christoph Moench-Tegeder :

> ## Guyren Howe (guy...@gmail.com):
>
> > I am inclined to advise folks to use PL/V8 on Postgres, because it is
> > a reasonable language, everyone knows it, it has good string functions,
> > decent performance and it tends to be installed everywhere (in
> particular,
> > Amazon RDF offers it).
>
> I'd be careful with "everyone knows JavaScript" - that may hold in
> web development, but there are many cases where Javascript is completely
> off the map. And for V8 itself - it adds quite a chunk of code to your
> PostgreSQL installation, that may put some people off. At least, I
> haven't seen it installed "generally" in the wild, but my view might
> be as biased as anyone else's.
>
> > Broadly, what advice should I offer that isn’t obvious? Not just about
> > PL/V8 but server side code in general.
>
> Initially, running code in your database can make life easier for
> the developers (ise pgTap for testing, pl/profiler and pl/debugger,
> etc.). But once you have to change your schema, the hurt begins:
> you'll need downtime for that, or you'll have to deal with the
> possibility of changing the API of your "database side" code, and
> matching code and tables on the database. There have been talks
> about that topic (using versioned schemas and leveraging search_path),
> but still: a simple change to a function suddenly requires a lot
> of care.
>

you are forgot on reduction of network cost - when some task generates lot
of fast queries, then main bottleneck is a network. Stored procedures
removes this bottleneck.

PLpgSQL shares data formats and process with PostgreSQL database engine -
there are not data conversations, there are not network/protocols/API
overhead, there are not interprocess communication overhead.



> The next pain point is scalability: running code on the database server
> puts your code on the most expensive and hardest to scale CPUs. You
> can (almost) always add another appserver to your setup (just spin
> up a VM with a tomcat or whatever-you-use). But if the bottleneck
> is your database CPUs, you'd have to move to a larger server (that
> got easier with VMs, within limits); or use replication to offload
> some code to standbys, keeping writing code on the primary (and
> hope you'll have enough horsepower there). Multi-Master introduces
> some special limitations and operational overhead on it's own, I'd
> not generally recommend that for all applications and developers
> just moving up from the "dump data bucket" model.
>
> TL;DR: database side code can be a great thing in a small application,
> but once the application and traffic grows, "code in the database"
> requires specialist attention and may become a burden.
> Unfortunately, most large applications started small...
>

When you use stored procedures, you have to choose well the border - what
should be done by server, what should be done by outside. Usually stored
procedures should be glue of SQL - and then the overhead of stored
procedures is near to zero. Surely, stupid ORM techniques has terrible
impact on server side.

regards

Pavel



>
> Regards,
> Christoph
>
> --
> Spare Space
>
>
> --
> 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] LYDB: What advice about stored procedures and other server side code?

[Christoph Moench-Tegeder]

## Guyren Howe (guy...@gmail.com):

> I am inclined to advise folks to use PL/V8 on Postgres, because it is
> a reasonable language, everyone knows it, it has good string functions,
> decent performance and it tends to be installed everywhere (in particular,
> Amazon RDF offers it).

I'd be careful with "everyone knows JavaScript" - that may hold in
web development, but there are many cases where Javascript is completely
off the map. And for V8 itself - it adds quite a chunk of code to your
PostgreSQL installation, that may put some people off. At least, I
haven't seen it installed "generally" in the wild, but my view might
be as biased as anyone else's.

> Broadly, what advice should I offer that isn’t obvious? Not just about
> PL/V8 but server side code in general.

Initially, running code in your database can make life easier for
the developers (ise pgTap for testing, pl/profiler and pl/debugger,
etc.). But once you have to change your schema, the hurt begins:
you'll need downtime for that, or you'll have to deal with the
possibility of changing the API of your "database side" code, and
matching code and tables on the database. There have been talks
about that topic (using versioned schemas and leveraging search_path),
but still: a simple change to a function suddenly requires a lot
of care.
The next pain point is scalability: running code on the database server
puts your code on the most expensive and hardest to scale CPUs. You
can (almost) always add another appserver to your setup (just spin
up a VM with a tomcat or whatever-you-use). But if the bottleneck
is your database CPUs, you'd have to move to a larger server (that
got easier with VMs, within limits); or use replication to offload
some code to standbys, keeping writing code on the primary (and
hope you'll have enough horsepower there). Multi-Master introduces
some special limitations and operational overhead on it's own, I'd
not generally recommend that for all applications and developers
just moving up from the "dump data bucket" model.

TL;DR: database side code can be a great thing in a small application,
but once the application and traffic grows, "code in the database"
requires specialist attention and may become a burden.
Unfortunately, most large applications started small...

Regards,
Christoph

-- 
Spare Space


-- 
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] Performance PLV8 vs PLPGSQL

[Mike Sofen]

From: Tim Uckun
I have seen various links on the internet which indicate that PLV8 is 
significantly faster than PL-PGSQL sometimes an order of magnitude faster. 

 

Is there any benefit to choosing PL-PGSQL?



I can’t speak to PLV8.  However, I can speak to plpgsql, and specifically 
stored functions (procs).  I use it exclusively to create a database API for 
real-time web applications to hit.  My API calls (procs) are hitting large 
tables, sometimes doing complex logic within the sproc.  It allows me to 
provide a simple, standardized interface to the web devs, allowing them to 
focus on the app code work.

 

Performance is superb and continues to surprise me (I came from the SQL Server 
world).  As others have mentioned, the natural lashup of plpgsql to postgres (I 
liked Alban’s term, “impedance”), is a key aspect.  Also:

 

-stored procs provide another security layer against sql injection 
attacks.

-Caching SEEMS to be more efficient/effective with stored procs (that 
could be wishful thinking too).

-Stored procs allow skilled sql practitioners to provide far more 
sophisticated sql solutions than the typical python developer is capable of…my 
experience is that most web devs don’t really understand databases (or even 
care about them – they are a necessary evil), so providing a pure encapsulated 
sql solution (via stored procs) removes that mental impedance mismatch.

-Performance?  Simple “get” procs that return data for a specific 
indexed query against larger tables (50m+ rows) in a few milliseconds…I can 
live with that kind of performance.

-I’m also doing some heavy lifting in the sql, calculating histograms 
and boxplots for data visualizations.  This is an unusual scenario, but the 
other option is sending a massive chunk of data to another server for 
processing – just the transit time would kill the deal.  I am mindful that at a 
certain point, there won’t be enough memory and i/o to go around, but the web 
app is a low user count/high user task complexity app, so I’ve tailored the 
model to match.

 

Mike Sofen  (Synthetic Genomics)

Re: [GENERAL] Generating sample data

[Rich Shepard]

On Wed, 28 Dec 2016, Martijn Tonies (Upscene Productions) wrote:


Not open source, but also not pricey (IMO): Advanced Data Generator.
http://www.upscene.com/advanced_data_generator/

Generates e-mail addresses, street names, first & last names, company names,
complex relationships etc.

And yes, this is our product. ;)


Martijn,

  Thank you for making me aware of your company and product. However, after
20 years of using only F/OSS to run my business (and personal computing)
needs and contributing to several open source projects along the way my
preference is to use such tools. When I get the large database application
up and running I'll post it on github and turn it loose into the F/OSS world
under the GPL.

Regards,

Rich


--
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] Performance PLV8 vs PLPGSQL

[Pavel Stehule]

2016-12-28 10:46 GMT+01:00 Pavel Stehule :

> Hi
>
> 2016-12-28 10:15 GMT+01:00 Tim Uckun :
>
>> I have seen various links on the internet which indicate that PLV8 is
>> significantly faster than PL-PGSQL sometimes an order of magnitude faster.
>>
>> Is this uniformly true or is it just in certain circumstances?
>>
>
> It depends on usage
>
>
>>
>> Is there any benefit to choosing PL-PGSQL?
>>
>
> there are more benefits
>
> 0. this language has integrated SQL - the code with lot of SQL is more
> readable.
> 1. it is everywhere, where is Postgres
> 2. it uses same data types like Postgres, there is not any conversion
> related overhead
> 3. this engine has quick start, faster than anything else.
>

4. It is not slow language - it is AST interpret - it is significantly
faster than byte code based interpreter without JIT. The expression are
slower - the PostgerSQL expression interpret. This is bottle neck of
PLpgSQL performance - on second hand - it is main benefit of PLpgSQL -
there is full integration with PostgreSQL runtime without any exceptions.

Years ago PLpgSQL has very slow operations on arrays - this was fixed in
9.5. Still PLpgSQL has slow string updates - Strings are immutable in
PLpgSQL - so update means generating new string. But usually it is not
bottleneck in PL environment.

Regards

Pavel


>
>
>>
>> Is there work going on to make PL-PGSQL more performant or has it gotten
>> significantly faster in the last two years or so (some of the links are a
>> from a while ago).
>>
>
> What I know no. There is not any reason why to do it. This language is
> designed be glue of SQL statements. Nothing more, nothing less. I did
> tests, and it is significantly faster than SQL engine.
>
> Some years ago I though about compilation to C language, but the target
> was better obfuscation not performance.
>
> PLpgSQL is not designed for hard calculation - the critical path is in SQL
> always. If you need different performance, than you can use PLV8, PLPython
> or native C extension. Postgres has very good API for writing C extensions.
>
> Regards
>
> Pavel
>
>
>>
>> Thanks.
>>
>
>

Re: [GENERAL] Performance PLV8 vs PLPGSQL

[Pavel Stehule]

Hi

2016-12-28 10:15 GMT+01:00 Tim Uckun :

> I have seen various links on the internet which indicate that PLV8 is
> significantly faster than PL-PGSQL sometimes an order of magnitude faster.
>
> Is this uniformly true or is it just in certain circumstances?
>

It depends on usage


>
> Is there any benefit to choosing PL-PGSQL?
>

there are more benefits

0. this language has integrated SQL - the code with lot of SQL is more
readable.
1. it is everywhere, where is Postgres
2. it uses same data types like Postgres, there is not any conversion
related overhead
3. this engine has quick start, faster than anything else.


>
> Is there work going on to make PL-PGSQL more performant or has it gotten
> significantly faster in the last two years or so (some of the links are a
> from a while ago).
>

What I know no. There is not any reason why to do it. This language is
designed be glue of SQL statements. Nothing more, nothing less. I did
tests, and it is significantly faster than SQL engine.

Some years ago I though about compilation to C language, but the target was
better obfuscation not performance.

PLpgSQL is not designed for hard calculation - the critical path is in SQL
always. If you need different performance, than you can use PLV8, PLPython
or native C extension. Postgres has very good API for writing C extensions.

Regards

Pavel


>
> Thanks.
>

[GENERAL] Performance PLV8 vs PLPGSQL

[Tim Uckun]

I have seen various links on the internet which indicate that PLV8 is
significantly faster than PL-PGSQL sometimes an order of magnitude faster.

Is this uniformly true or is it just in certain circumstances?

Is there any benefit to choosing PL-PGSQL?

Is there work going on to make PL-PGSQL more performant or has it gotten
significantly faster in the last two years or so (some of the links are a
from a while ago).

Thanks.

Re: [GENERAL] Generating sample data

[Martijn Tonies (Upscene Productions)]

Hi,

Not open source, but also not pricey (IMO): Advanced Data Generator.
http://www.upscene.com/advanced_data_generator/

Generates e-mail addresses, street names, first & last names, company names,
complex relationships etc.

And yes, this is our product. ;)

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com



  My previous databases used real client (or my own) data; now I want to
generate sample data for the tables in the two applications I'm developing.
My web search finds a bunch of pricey (IMO) commercial products.

  Are there any open source data generators that can provide sample data
based on each table's schema?

TIA,

Rich




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




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