Re: [GENERAL] Postgres and data warehouses

[Rob Sargent]

I strongly suspect many would like further details on your implementation. A 
user conference session well recorded perhaps?

> On Mar 8, 2015, at 4:57 PM, John R Pierce  wrote:
> 
>> On 3/8/2015 7:40 AM, Nigel Gardiner wrote:
>> I'm looking at making a data warehouse to address our rapidly spiralling 
>> report query times against the OLTP. I'm looking first at what it would take 
>> to make this a real-time data warehouse, as opposed to batch-driven.
> 
> we use a hybrid architecture.we have a 'republisher' process that 
> repeatedly slurps new data from the OLTP database and sends it to the back 
> end databases, using a 'publish/subscribe' messaging bus.several back end 
> databases subscribe to this data, and their subscriber processes insert the 
> incoming data into those OLAP and various other reporting databases.   this 
> way the reporting databases can have completely different schemas optimized 
> for their needs, and have different retention requirements than the OLTP 
> database.
> 
> this republisher is usually within a few seconds of live new data. in our 
> case its made fairly easy to track 'new' because all our OLTP transactions 
> are event-oriented.
> 
> 
> -- 
> john r pierce  37N 122W
> somewhere on the middle of the 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


-- 
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] Postgres and data warehouses

[Rob Sargent]

I expected the latter but … some proprietors like to brag :)

Standing down.

> On Mar 8, 2015, at 9:56 PM, John R Pierce  wrote:
> 
> On 3/8/2015 8:24 PM, Rob Sargent wrote:
>> I strongly suspect many would like further details on your implementation. A 
>> user conference session well recorded perhaps?
> 
> the details are proprietary and quite specific to our workload and 
> requirements.
> 
> 
> 
> -- 
> john r pierce  37N 122W
> somewhere on the middle of the 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



-- 
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] Finding values of bind variables

[Rob Sargent]

> On Apr 8, 2015, at 3:43 PM, Vasudevan, Ramya  
> wrote:
> 
> Thank You John.
>  
> > the state_change to idle-in-transaction took place about 0.0001 seconds 
> > later (so the insert took all of a millisecond).
> Yes, the DML “finished” but stayed uncommitted.
> 
> > What date/time was it when you did that select from pg_stat_activity ?
> > thats how long this transaction has been pending, which if its more than a 
> > few seconds to at most a minute, is almost certainly a 'very bad thing' 
> > unless the transaction is actively crunching data.
> 2015-04-07 14:25 – ~17 hours after the DML ran.
> Agreed, it was bad. There was nothing happening with this transaction since 
> 2015-04-06 21:16:26. It was just sitting there.
> 
> > if you're using a client such as JDBC which autowraps queries in 
> > transactions by default, you need to invoke COMMIT's when you're done doing 
> > that unit of work.
> This is what I am trying to confirm from the application side. It will help 
> the dev team if they have the value for bind variables apparently.
> 
> Any idea on how to get the values for bind variables for a transaction like 
> this?
> 
> Thank You
> Ramya

I don’t recall seeing what stack this application is using, but it’s probably 
easier for the dev to start collecting the “bind variables”.  (A simple config 
in hibernate, for instance)
If you’re logging sql at the server, then you have the values somewhere in the 
logs, else “it doesn’t matter, it is in the past”

Re: R: [GENERAL] Index on integer or on string field

[Rob Sargent]

Are you saying your indexed field has only 50 distinct values? Seems a horrible 
candidate for an index. Might be good to partition on those fifty values but 
ten million records probably doesn't warrant that. 

Sent from my iPhone

> On May 15, 2015, at 9:34 AM, Job  wrote:
> 
> Hello Arthur!
> 
> So, i read that btree-gin have got "the ability to enforce uniqueness".
>  
> If in this 10.millions long table i have, in index, 50 recurring values, i 
> can leave the alphabetical field and change to btree-gin the index on it?!
> 
> Thank you!
> Francesco
>  
> Da: Arthur Silva [arthur...@gmail.com]
> Inviato: venerdì 15 maggio 2015 17.26
> A: Job
> Cc: pgsql-general@postgresql.org
> Oggetto: Re: [GENERAL] Index on integer or on string field
> 
> You should probably experiment with a btree-gin index on those.
> Em 15/05/2015 12:22, "Job"  escreveu:
>> Hello,
>> 
>> i have a table of about 10 millions of records, with the index on a string 
>> field.
>> Actually is alphabetical; since queries are about 100/200 per seconds, i was 
>> looking for a better way to improve performance and reduce workload.
>> 
>> The unique values, of that fields, are about the 50 (category name), and we 
>> could create a second table to codify, with numerical integer values, the 50 
>> recurring names.
>> 
>> Is index are integer and not characteral, performance are better and 
>> workload reduces?
>> 
>> Is there any comparisons?
>> 
>> Thank you!
>> Francesco
>> 
>> --
>> 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] Error prone compilation of stored procedure

[Rob Sargent]

And are your colleagues offering to pay for an Oracle license?

> On Jul 6, 2015, at 5:52 AM, pinker  wrote:
> 
> Pavel Stehule wrote
>> PLpgSQL doesn't check a identifiers inside embedded SQL before execution.
>> In this case j_var can be theoretically some SQL identifiers - the
>> possibility or impossibility is not know in function validation stage.
>> 
>> It has some plus and minus points. The plus - there are not strong
>> dependency between database objects and PL code. The minus - lot of bugs
>> are not detected in validation stage. But this issue can be solved by
>> plpgsql_check extension https://github.com/okbob/plpgsql_check/
> 
> Thank you for the link to extension. 
> Another minus is that my colleagues which use to work on oracle think that
> postgresql is at least one league below oracle.
> 
> 
> 
> --
> View this message in context: 
> http://postgresql.nabble.com/Error-prone-compilation-of-stored-procedure-tp5856699p5856708.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



-- 
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] Delete rule does not prevent truncate

[Rob Sargent]

On 07/23/2015 12:09 PM, Adrian Klaver wrote:

On 07/23/2015 04:57 AM, Tim Smith wrote:

Andrew,

 From the manual:

It is important to realize that a rule is really a command
transformation mechanism, or command macro. The transformation happens
before the execution of the command starts. If you actually want an
operation that fires independently for each physical row, you probably
want to use a trigger, not a rule


Thus, I should not have to use a trigger for TRUNCATE because the "each
row" concept does not apply. Plus it makes perfect sense to want to
transform the truncate command and transform into ignore



Just in case it has not been made obvious yet, rules are silently 
deprecated. They still exist because views depend on them, but it is 
generally considered best practices to not use them outside that 
realm. So if you want the rule behavior to change for TRUNCATE(if that 
is even possible) you are fighting an uphill battle. You may pursue 
that fight of course, but I would think you will get a quicker return 
on your time if you just forget about using a RULE and stick to a 
TRIGGER instead.



Or change to using delete instead of truncate?

Re: [GENERAL] Delete rule does not prevent truncate

[Rob Sargent]

On 07/23/2015 12:25 PM, Adrian Klaver wrote:

On 07/23/2015 11:15 AM, Rob Sargent wrote:

On 07/23/2015 12:09 PM, Adrian Klaver wrote:

On 07/23/2015 04:57 AM, Tim Smith wrote:

Andrew,

 From the manual:

It is important to realize that a rule is really a command
transformation mechanism, or command macro. The transformation happens
before the execution of the command starts. If you actually want an
operation that fires independently for each physical row, you probably
want to use a trigger, not a rule


Thus, I should not have to use a trigger for TRUNCATE because the 
"each
row" concept does not apply. Plus it makes perfect sense to 
want to

transform the truncate command and transform into ignore



Just in case it has not been made obvious yet, rules are silently
deprecated. They still exist because views depend on them, but it is
generally considered best practices to not use them outside that
realm. So if you want the rule behavior to change for TRUNCATE(if that
is even possible) you are fighting an uphill battle. You may pursue
that fight of course, but I would think you will get a quicker return
on your time if you just forget about using a RULE and stick to a
TRIGGER instead.


Or change to using delete instead of truncate?



Well Tim has an ON DELETE rule:

http://www.postgresql.org/message-id/CA+HuS5G2bZYYOGTJrw+VosjUPO298swxuU=jorfav54ut7v...@mail.gmail.com 



His expectation was that would also catch a TRUNCATE based on this:

"... It has the same effect as an unqualified DELETE on each table, ..."

from here:

http://www.postgresql.org/docs/9.4/interactive/sql-truncate.html

It was then explained that while TRUNCATE had the same end result as 
'DELETE FROM some_table' it was actually a separate command and 
action. Tim wants to catch a TRUNCATE and turn it into an ignore.



I'm suggesting OP might find changing truncate statements to deletes 
(without a where clause) a simpler solution. Something has to change.

Re: [GENERAL] Delete rule does not prevent truncate

[Rob Sargent]

On 07/23/2015 04:15 PM, Karsten Hilbert wrote:

On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote:


I'm suggesting OP might find changing truncate statements to deletes
(without a where clause) a simpler solution. Something has to change.

Well, OP isn't looking for a solution to "delete all rows"
but rather to _prevent_ deletion.

Tim can't go forth and tell Blackhats to "please use DELETE
rather than TRUNCATE", right ?

AFAICT it'd be more useful to advise OP to revoke TRUNCATE
rights on tables.

Karsten
Not sure about Tim and the Blackhats (there's a band name in there 
somewhere) but Wouldn't OP have exact same code to fix, one way or another?

Re: [GENERAL] Delete rule does not prevent truncate

[Rob Sargent]

On 07/23/2015 06:27 PM, Adrian Klaver wrote:

On 07/23/2015 05:08 PM, Rob Sargent wrote:

On 07/23/2015 04:15 PM, Karsten Hilbert wrote:

On Thu, Jul 23, 2015 at 12:28:32PM -0600, Rob Sargent wrote:


I'm suggesting OP might find changing truncate statements to deletes
(without a where clause) a simpler solution. Something has to change.

Well, OP isn't looking for a solution to "delete all rows"
but rather to _prevent_ deletion.

Tim can't go forth and tell Blackhats to "please use DELETE
rather than TRUNCATE", right ?

AFAICT it'd be more useful to advise OP to revoke TRUNCATE
rights on tables.

Karsten

Not sure about Tim and the Blackhats (there's a band name in there
somewhere) but Wouldn't OP have exact same code to fix, one way or 
another?




I think the point was, the OP(Tim) might not have access to the code 
that is trying to TRUNCATE. This could be because it is coming from 
authorized users who are writing their own code or unauthorized 
users(Blackhats) who are trying to sneak code in.



Fair enough but both blackhats and the authorized are just as likely to 
drop the database as truncate something (intentionally or not) and 
backups stashed everywhere is the first order of business.

Re: [GENERAL] PostgreSQL Developer Best Practices

[Rob Sargent]

> On Aug 24, 2015, at 6:53 PM, Melvin Davidson  wrote:
> 
> You are right, he was probably talking about FK's. I was just so frustrated 
> about people insisting that using "ID" as the primary key in every table is a 
> "good" idea,
> I didn't bother to reply previously. I stand firm on my belief that the 
> primary key should be something meaningful and NOT "id" just for the sake of 
> having a unique numeric key.
> 
What, pray tell, is the unique natural key of person in any meaningfully large 
domain such as state? Certainly not name + birthdate.  Current address isn’t 
guaranteed. Social isn’t reliable and actually not truly unique.

Even given that there are models which are made of entities with legitimate 
attributes which per force define a unique instance, I see no benefit in 
avoiding the convenience of an arbitrary and simple value for the key.  Is it 
the overhead of generating and storing one more value per tuple that you can’t 
abide?




-- 
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] PostgreSQL Developer Best Practices

[Rob Sargent]

On 08/25/2015 09:40 AM, Melvin Davidson wrote:

Adrian,

Stop being so technical. When we/I speak of natural keys, we are 
talking about the column

that would NATURALly lend itself as the primary key.
No one ever said a number is not natural. just that there is no need 
to duplicate uniqueness

with a separate number.

IOW: If we have an account table, then the account_id or account_no
 would be the primary key. There is no need to have a separate 
serial id as the primary key.
If I'm following correctly, you're saying that if the definition of the 
entity contains and arbitrary unique value then use that. Fine.  I guess 
I quibble with the notion of VIN as a "natural" attribute of car.  (I 
have no firsthand experience with VINs but I would bet there's 
information tucked inside them, which would make me sceptical of using 
them :) )



--
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] PostgreSQL Developer Best Practices

[Rob Sargent]

On 08/25/2015 04:27 PM, Gavin Flower wrote:

On 26/08/15 04:33, Marc Munro wrote:

On Sat, 2015-08-22 at 15:15 +, Melvin Davidson wrote:

I've been searching for a "PostgreSQL Developer Best Practices" with 
not

much luck,
so I've started my own. At the risk of stirring up a storm of 
controversy,

I would appreciate additional suggestions and feedback.


You might add: Create all relation names as plurals.

Or, if your site uses predominantly singular names, make that the
standard.  Consistency within the site is more important than any
dogmatic belief about whether singular or plural forms is better.  If
you don't put it in the standard, someone will eventually create tables
with names that don't gel with everything else.

__
Marc





Actually I would suggest standardising on singular names, not JUST 
because that this the standard I prefer!  :-)


But (also) because:

1. Singular words tend to be shorter

2. plurals are more ambiguous wrt spelling

3. there other good reasons, that I've forgotten for now :-(
   (but I remember having them!!!)



4. Each tuple is an instance of entity, not entities :)

Cheers,
Gavin






--
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] Most effective settings for deleting lots of data?

[Rob Sargent]

On 08/26/2015 02:34 PM, Alan Hodgson wrote:

On Wednesday, August 26, 2015 08:25:02 PM Cory Tucker wrote:

What settings would you recommend?  Also, it just occurred to me that I
should try to disable/drop all indexes (especially since they will be
recreated) later so that those are not updated in the process.

Don't drop the indexes your foreign keys use to find cascading deletes.




Or do drop indexes and walk up your dependency graph manually.

Re: [GENERAL] get first and last row in one sql as two columns

[Rob Sargent]

On 09/02/2015 05:14 PM, Tom Smith wrote:

Hi:

I need to get the first and last tow in one sql like below

select first(col1), last(col1) from table order by col1

I saw some posting in wiki with a custom function (or C extention)
to do this.   Is it widely used and reliable?
https://wiki.postgresql.org/wiki/First/last_(aggregate) 



I am wondering why these two functions are not part of postgresql built-in
functions as it has many use cases

Thanks

If you're ordering by col1, does

   select min(col1), max(col1) from table order by col1

not do the trick;

Re: [GENERAL] get first and last row in one sql as two columns

[Rob Sargent]

On 09/02/2015 05:14 PM, Tom Smith wrote:

Hi:

I need to get the first and last tow in one sql like below

select first(col1), last(col1) from table order by col1

I saw some posting in wiki with a custom function (or C extention)
to do this.   Is it widely used and reliable?
https://wiki.postgresql.org/wiki/First/last_(aggregate) 



I am wondering why these two functions are not part of postgresql built-in
functions as it has many use cases

Thanks
But what I think you are looking for are the windowing functions as what 
you propose breaks down pretty quickly with more complicated queries.

Re: [GENERAL] Import Problem

[Rob Sargent]

On 09/16/2015 09:27 AM, Ramesh T wrote:

Hi All,
   I'm using or2pg tool ,I exported data but I have to change 
the schema import to postgres database.exported data more than gb.
Can you please let me know, how to do the change the name in data 
script..?

sed is your friend, if you have ascii data.

Re: [GENERAL] using postgresql for session

[Rob Sargent]

On 10/07/2015 10:53 AM, Bill Moran wrote:

On Wed, 7 Oct 2015 09:58:04 -0600
"john.tiger"  wrote:


has anyone used postgres jsonb for holding session ?  Since server side
session is really just a piece of data, why bother with special
"session" plugins and just use postgres to hold the data and retrieve it
with psycopg2 ?  Maybe use some trigger if session changes?We are
using python Bottle with psycopg2 (super simple, powerful combo) - are
we missing something magical about session plugins ?

Nothing that I'm aware of. I've worked on large projects that keep the
session data in a Postgres table with great success.

Previous job gave up on mongo and switched to postgres since it was 
faster, more reliable.

Re: [GENERAL] ID column naming convention

[Rob Sargent]

On 10/13/2015 11:36 AM, droberts wrote:

Hi, is there a problem calling ID's different when used as a FK vs table ID?
For example


mydimtable ()
  ID
  name
  description


myfacttable ()
   my_dim_id   # FK to ID above
   total_sales


I 'think' if I don't enforce foreign key constraints, then this practice
prevents tools from being able to generate ERD diagrams right?



--
View this message in context: 
http://postgresql.nabble.com/ID-column-naming-convention-tp5869844.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Pretty sure _all_ ER diagramming tools rely on the DDL of column 
constraints not the names.  And personally I would name the column 
mydim_id. i.e don't inject the extra underscore which is not in your 
table name. (And I assume the table names are fake:  you don't really 
want "table" in table name)

Re: [GENERAL] Simple way to load xml into table

[Rob Sargent]

On 10/15/2015 11:38 AM, Emi wrote:

Hello,

For psql 8.3, is there a simple way to load xml file into table please?

E.g.,

  
True
test1
e1
  
  
false
test2
  

Results:
t1 (c1 text, c2 text, c3 text):

c1| c2 | c3
-
true| test1 | e1
false   | test2 | null
..

Thanks a lot!



Shame on Concordia! 8.3.  Really? (Send this up the chain)

Re: [GENERAL] Where do I enter commands?

[Rob Sargent]

ok. now who has the url to the pithy 
heres-why-you-really-want-the-command-line.  

It distills to something about actually knowing what you’re doing.


> On Oct 24, 2015, at 9:29 PM, David Blomstrom  
> wrote:
> 
> Good tip; I can now see the database I created. Thanks.
> 
> On Sat, Oct 24, 2015 at 8:20 PM, Adrian Klaver  > wrote:
> On 10/24/2015 08:00 PM, David Blomstrom wrote:
> "Is there a entry under Servers?"
> 
> PostgreSQL 9.5 (localhost) - but there's a red X over it.
> 
> That means you are not connected to the Server. Right click on the entry and 
> select Connect. It will probably ask for a password, which should be the 
> database password you created when you did the install.
> 
> 
> On Sat, Oct 24, 2015 at 7:52 PM, Adrian Klaver
> mailto:adrian.kla...@aklaver.com> 
> >> wrote:
> 
> On 10/24/2015 07:44 PM, David Blomstrom wrote:
> 
> Hmmm...I have pgAdminIII. When I click on Server, there's no
> option to
> create a database.
> 
> 
> I would spend some time here:
> 
> http://www.pgadmin.org/docs/1.20/index.html 
> 
> 
> before going much further, just to get the gist of pgAdmin.
> 
> In the meantime, you have to connect to the Server before you can
> create anything on it. Is there a entry under Servers?
> 
> 
> On Sat, Oct 24, 2015 at 7:37 PM, John R Pierce
> mailto:pie...@hogranch.com> 
> >
>  
>  
>  On 10/24/2015 7:33 PM, David Blomstrom wrote:
> 
>  I'd greatly prefer a GUI. It seems like a command-line tool
>  would be incredibly tedious when creating tables, modifying
>  them, filling them with data, etc. Thanks.
> 
> 
>  normally, your application programs do the data filling
> part, manual
>  data entry direct via sql is fairly uncommon.
> 
>  anyways, the GUI is pgAdmin, and you can create a database by
>  opening the server, and selecting 'new database...', give it an
>  owner, etc.   then open that database, find hte Public schema
>  therewithin, and create your table(s) in that schema...
> 
>  --
>  john r pierce, recycling bits in santa cruz
> 
> 
> 
>  --
>  Sent via pgsql-general mailing list
> (pgsql-general@postgresql.org  
> >
>   
>  >>)
>  To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general 
> 
> 
> 
> 
> 
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org   > >
> 
> 
> 
> --
> Adrian Klaver
> adrian.kla...@aklaver.com  
> >
> 
> 
> 
> 
> --
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org   >
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 
> 
> 
> 
> -- 
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org 

Re: [GENERAL] Where do I enter commands?

[Rob Sargent]

> On Oct 25, 2015, at 3:21 AM, Joshua D. Drake  wrote:
> 
> I would ignore Rob, he obviously is suffering from a lack of coffee. Our 
> community always tries to help new users. It is great to see you here.
> 
> Sincerely,
> 
> JD
> 

Always the best advice :)

OK, Coffee’d up now.  Apologies for my grumpiness. The rest of the thread says 
it much more nicely.




-- 
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] Recursive Arrays 101

[Rob Sargent]

On 10/26/2015 08:43 AM, Jim Nasby wrote:

On 10/25/15 8:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so many
species - especially fish - the spreadsheets I use to organize them are
just about maxed out as it is.


The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.
Seems to me that if life boils down to four attributes one would have a 
single table with those four attributes on the particular life form.  
Now, the four attributes could be ids into definitional tables but I 
suspect the querying will be done string/name so why complicate the 
lookups: make the names a foreign key in the defs if necessary.


Personally I think the recursive structure is the way to go.

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

On 10/26/2015 09:22 AM, Adrian Klaver wrote:

On 10/26/2015 08:12 AM, Rob Sargent wrote:

On 10/26/2015 08:43 AM, Jim Nasby wrote:

On 10/25/15 8:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign 
keys and

see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so 
many
species - especially fish - the spreadsheets I use to organize them 
are

just about maxed out as it is.


The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.

Seems to me that if life boils down to four attributes one would have a
single table with those four attributes on the particular life form.


Out of curiosity what are those four attributes? It would have made 
memorizing all those organisms a lot easier when I was in school:)


kingdom phylum class genus as attributes in species table.  Talk about 
your "natural key".  The hibernate boys would love it :)



Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.


Jtbc, I'm not advocating this structure but it may suit the OP's usage 
patterns.





--
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] Recursive Arrays 101

[Rob Sargent]

On 10/26/2015 11:14 AM, Adrian Klaver wrote:

On 10/26/2015 08:32 AM, Rob Sargent wrote:

On 10/26/2015 09:22 AM, Adrian Klaver wrote:

On 10/26/2015 08:12 AM, Rob Sargent wrote:

On 10/26/2015 08:43 AM, Jim Nasby wrote:

On 10/25/15 8:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign
keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so
many
species - especially fish - the spreadsheets I use to organize them
are
just about maxed out as it is.


The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.
Seems to me that if life boils down to four attributes one would 
have a

single table with those four attributes on the particular life form.


Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)


kingdom phylum class genus as attributes in species table.  Talk about
your "natural key".  The hibernate boys would love it :)


Well in this classification system it would need to be:

kingdom phylum class order family genus

Sorry, wasn't tracking carefully: 6 attributes


What makes it complicated is that these are just the slots. How 
organisms are slotted depends on attributes and there are a lot of 
them. This means there is a constant rearrangement in the slotting.


But at the end of the day, is it not the intent to have those six filled 
per species. Is your point that maintenance would be problematic?  
Agreed.  Certainly not just a single pointer redirect in a recursive 
structure.  All depends on OPs usage patterns.  I personally love 'with 
recursion' but it's more complicated than for example

select count(*) from species where class = ''
if, and only if, all 6 attributes are always there.  Which highlights 
your caveat "In this classification system".



Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.



Jtbc, I'm not advocating this structure but it may suit the OP's usage
patterns.









--
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] Recursive Arrays 101

[Rob Sargent]

On 10/26/2015 02:29 PM, David Blomstrom wrote:
Sorry for the late response. I don't have Internet access at home, so 
I only post from the library or a WiFi cafe.


Anyway, where do I begin?

Regarding my "usage patterns," I use spreadsheets (Apple's Numbers 
program) to organize data. I then save it as a CSV file and import it 
into a database table. It would be very hard to break with that 
tradition, because I don't know of any other way to organize my data.


On the other hand, I have a column (Rank) that identifies different 
taxonomic levels (kingdom, class, etc.). So I can easily sort a table 
into specific taxonomic levels and save one level at a time for a 
database table.


There is one problem, though. I can easily put all the vertebrate 
orders and even families into a table. But genera might be harder, and 
species probably won't work; there are simply too many. My spreadsheet 
program is almost overwhelmed by fish species alone. The only solution 
would be if I could import Mammals.csv, then import Birds.csv, 
Reptiles.csv, etc. But that might be kind of tedious, especially if I 
have to make multiple updates.


Yes I suspect you spreadsheet will be limited in rows, but of course you 
can send all the spreadsheets to a single table in the database. If 
that's what you want.  You don't have to, but you see mention of tables 
millions of records routinely.  On the other hand, if performance 
becomes an issue with the single table approach you might want to look 
at "partitioning".  But I would be surprised if you had to go there.


What is your data source?  How much hand-entry are you doing? There are 
tools which (seriously) upgrade the basic 'COPY into ' command.


As for "attributes," I'll post my table's schema, with a description, 
next.






--
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] Recursive Arrays 101

[Rob Sargent]

On 10/26/2015 02:51 PM, David Blomstrom wrote:
I'm focusing primarily on vertebrates at the moment, which have a 
total of (I think) about 60,000-70,000 rows for all taxons (species, 
families, etc.). My goal is to create a customized database that does 
a really good job of handling vertebrates first, manually adding a few 
key invertebrates and plants as needed.


I couldn't possibly repeat the process with invertebrates or plants, 
which are simply overwhelming. So, if I ever figure out the Catalogue 
of Life's database, then I'm simply going to modify its tables so they 
work with my system. My vertebrates database will override their 
vertebrate rows (except for any extra information they have to offer).


As for "hand-entry," I do almost all my work in spreadsheets. I spent 
a day or two copying scientific names from the Catalogue of Life into 
my spreadsheet. Common names and slugs (common names in a URL format) 
is a project that will probably take years. I might type a scientific 
name or common name into Google and see where it leads me. If a 
certain scientific name is associated with the common name "yellow 
birch," then its slug becomes yellow-birch. If two or more species are 
called yellow birch, then I enter yellow-birch in a different table 
("Floaters"), which leads to a disambiguation page.


For organisms with two or more popular common names - well, I haven't 
really figured that out yet. I'll probably have to make an extra table 
for additional names. Catalogue of Life has common names in its 
database, but they all have upper case first letters - like American 
Beaver. That works fine for a page title but in regular text I need to 
make beaver lowercase without changing American. So I'm just starting 
from square one and recreating all the common names from scratch.


Multiple names can be handled in at least two ways.  A child table of 
species which has species id and alternate name per record - then you 
can get all other-names back by species id.  Of course going from 
altername-name back to species may get you multiple species. Or, welcome 
to postgres' arrays-as-column: you can have one column, maybe called 
aliases which is an array of string.


It gets still more complicated when you get into "specialist names." 
;) But the system I've set up so far seems to be working pretty nicely.


On Mon, Oct 26, 2015 at 1:41 PM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote:


On 10/26/2015 02:29 PM, David Blomstrom wrote:

Sorry for the late response. I don't have Internet access at
home, so I only post from the library or a WiFi cafe.

Anyway, where do I begin?

Regarding my "usage patterns," I use spreadsheets (Apple's
Numbers program) to organize data. I then save it as a CSV
file and import it into a database table. It would be very
hard to break with that tradition, because I don't know of any
other way to organize my data.

On the other hand, I have a column (Rank) that identifies
different taxonomic levels (kingdom, class, etc.). So I can
easily sort a table into specific taxonomic levels and save
one level at a time for a database table.

There is one problem, though. I can easily put all the
vertebrate orders and even families into a table. But genera
might be harder, and species probably won't work; there are
simply too many. My spreadsheet program is almost overwhelmed
by fish species alone. The only solution would be if I could
import Mammals.csv, then import Birds.csv, Reptiles.csv, etc.
But that might be kind of tedious, especially if I have to
make multiple updates.

Yes I suspect you spreadsheet will be limited in rows, but of
course you can send all the spreadsheets to a single table in the
database. If that's what you want.  You don't have to, but you see
mention of tables millions of records routinely.  On the other
hand, if performance becomes an issue with the single table
approach you might want to look at "partitioning".  But I would be
surprised if you had to go there.

What is your data source?  How much hand-entry are you doing?
There are tools which (seriously) upgrade the basic 'COPY into
' command.


As for "attributes," I'll post my table's schema, with a
description, next.





--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org <http://www.geobop.org>

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

On 10/26/2015 03:21 PM, Gavin Flower wrote:

On 27/10/15 10:17, David Blomstrom wrote:
What does "top post" mean? And what do you mean by "embedded spaces"? 
Are you referring to the underscores in the TABLE name?


On Mon, Oct 26, 2015 at 2:12 PM, Gavin Flower 
> wrote:


Hi David,

Please don't top post!


[...]

Top posting is when you put your reply at the top of the post, as you 
did just now in response to my post.


Here I am bottom posting, which is the norm for postgres mailing lists.

Ignore my comment about embedded spaces, I misread what you had 
written, underlines are fine.
But _really_ recommend lowercase column names and table names.  You'll 
have to quote them every time you want to use them in manual sql or scripts.

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

On 10/26/2015 05:28 PM, David Blomstrom wrote:

No, I'm on a Mac running OS X El Capitan.



I don't have my mac with me today so this is a little rough.

Cmd-Spacebar
Terminal

This should find the actual normal terminal.

Click on it.

at the prompt therein: psql --username davdi --host localhost

this should connect you to the db, I'm not sure what the prompt looks like

\l
will list the databases

\c 
\dt will list tables

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

You will do what you need to do but please do not claim that pg is not Mac 
compatible. Many on this thread are regular Mac/pg users. We all thought we 
were doing you a favour in trying to free you from GUI tools. For us they are 
restraining. For you they may be liberating. Stick with pgAdmin and ask 
questions about it. There may even be a forum dedicated to it. 

Someday we can revisit "power tools"

> On Oct 26, 2015, at 7:08 PM, David Blomstrom  
> wrote:
> 
> Judging from the anti-Mac comments, it sounds like PostgreSQL is kind of a 
> Linux/Microsoft thing. If PostgreSQL isn't compatible with Apple, then that's 
> definitely the end of the line. I used M$ for years and would never go back. 
> I used to be a passionate Linux supporter - largely because I wanted to see 
> it compete with Microsoft - but the Linux community never could understand 
> the concept of "user friendly."
> 
> I get far more service from my Mac than I ever got from M$, and I won't waste 
> my time with any software that isn't Mac-compatible.
> 
>> On Mon, Oct 26, 2015 at 6:01 PM, Melvin Davidson  
>> wrote:
>> The law of O/S & databases:
>> For every Linux / PostgreSQL user, there is and equal an opposite Mac / 
>> MySQL user.
>> However, the latter is completely useless.
>> 
>>> On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom 
>>>  wrote:
>>> That's exactly what I've been doing. I just did it again...
>>> 
>>> Last login: Mon Oct 26 17:53:05 on ttys001
>>> 
>>> Davids-MacBook-Pro-2:~ davidblomstrom$ 
>>> /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>>> 
>>> Server [localhost]: Server [localhost
>>> 
>>> Database [postgres]: Database [postgres]:
>>> 
>>> Port [5432]: Port [5432]:
>>> 
>>> Username [postgres]: Username [postgres]:
>>> 
>>> psql: warning: extra command-line argument "[5432]:" ignored
>>> 
>>> psql: warning: extra command-line argument "[postgres]:" ignored
>>> 
>>> psql: warning: extra command-line argument "Database" ignored
>>> 
>>> psql: warning: extra command-line argument "[postgres]:" ignored
>>> 
>>> psql: invalid port number: "Port"
>>> 
>>> 
>>> 
>>> Press  to continue...
>>> 
>>> 
 On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower 
  wrote:
> On 27/10/15 13:29, John R Pierce wrote:
>> On 10/26/2015 5:20 PM, David G. Johnston wrote:
>> What exactly are you showing us here?
> 
> he's demonstrating a lack of reading comprehension.   I'm done. thread on 
> ignore.
 I think its proof that Apple products rot your brain!
 
 
 
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general
>>> 
>>> 
>>> 
>>> -- 
>>> David Blomstrom
>>> Writer & Web Designer (Mac, M$ & Linux)
>>> www.geobop.org
>> 
>> 
>> 
>> -- 
>> Melvin Davidson
>> I reserve the right to fantasize.  Whether or not you 
>> wish to share my fantasy is entirely up to you. 
> 
> 
> 
> -- 
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

> On Oct 26, 2015, at 7:48 PM, David Blomstrom  
> wrote:
> 
> Doesn't this thread demonstrate the advantage of GUI's??? I created a 
> database and table with pgAdmin III with no help at all.
> 
> I then got a flurry of well-intentioned tips about the command-line tool. I 
> tried all of them. I tried them twice. I tried them upside down and 
> backwards. People then began complaining that I can't follow directions. Like 
> how many different ways are there to type in a command and hit enter???
> 
> I think it was obvious a couple dozen posts ago that there's something wrong 
> with my PostgreSQL installation or with the command-line tool.

I'm in lined to agree that your icon-generating-she'll thingy is suspect. Did 
you ever try the basic Terminal?  That puts you at the most basic command line 
talking to the operating system.   psql from there gets you to a database shell 
talking to your server.
You can indeed do a lot of what you want from pgadmin.  I'm mostly to blame for 
this thread so if you would like to take this up with me privately you're 
welcome to do so. 
> 
>> On Mon, Oct 26, 2015 at 6:43 PM, Rob Sargent  wrote:
>> You will do what you need to do but please do not claim that pg is not Mac 
>> compatible. Many on this thread are regular Mac/pg users. We all thought we 
>> were doing you a favour in trying to free you from GUI tools. For us they 
>> are restraining. For you they may be liberating. Stick with pgAdmin and ask 
>> questions about it. There may even be a forum dedicated to it. 
>> 
>> Someday we can revisit "power tools"
>> 
>>> On Oct 26, 2015, at 7:08 PM, David Blomstrom  
>>> wrote:
>>> 
>>> Judging from the anti-Mac comments, it sounds like PostgreSQL is kind of a 
>>> Linux/Microsoft thing. If PostgreSQL isn't compatible with Apple, then 
>>> that's definitely the end of the line. I used M$ for years and would never 
>>> go back. I used to be a passionate Linux supporter - largely because I 
>>> wanted to see it compete with Microsoft - but the Linux community never 
>>> could understand the concept of "user friendly."
>>> 
>>> I get far more service from my Mac than I ever got from M$, and I won't 
>>> waste my time with any software that isn't Mac-compatible.
>>> 
>>>> On Mon, Oct 26, 2015 at 6:01 PM, Melvin Davidson  
>>>> wrote:
>>>> The law of O/S & databases:
>>>> For every Linux / PostgreSQL user, there is and equal an opposite Mac / 
>>>> MySQL user.
>>>> However, the latter is completely useless.
>>>> 
>>>>> On Mon, Oct 26, 2015 at 8:54 PM, David Blomstrom 
>>>>>  wrote:
>>>>> That's exactly what I've been doing. I just did it again...
>>>>> 
>>>>> Last login: Mon Oct 26 17:53:05 on ttys001
>>>>> 
>>>>> Davids-MacBook-Pro-2:~ davidblomstrom$ 
>>>>> /Library/PostgreSQL/9.5/scripts/runpsql.sh; exit
>>>>> 
>>>>> Server [localhost]: Server [localhost
>>>>> 
>>>>> Database [postgres]: Database [postgres]:
>>>>> 
>>>>> Port [5432]: Port [5432]:
>>>>> 
>>>>> Username [postgres]: Username [postgres]:
>>>>> 
>>>>> psql: warning: extra command-line argument "[5432]:" ignored
>>>>> 
>>>>> psql: warning: extra command-line argument "[postgres]:" ignored
>>>>> 
>>>>> psql: warning: extra command-line argument "Database" ignored
>>>>> 
>>>>> psql: warning: extra command-line argument "[postgres]:" ignored
>>>>> 
>>>>> psql: invalid port number: "Port"
>>>>> 
>>>>> 
>>>>> 
>>>>> Press  to continue...
>>>>> 
>>>>> 
>>>>>> On Mon, Oct 26, 2015 at 5:49 PM, Gavin Flower 
>>>>>>  wrote:
>>>>>>> On 27/10/15 13:29, John R Pierce wrote:
>>>>>>>> On 10/26/2015 5:20 PM, David G. Johnston wrote:
>>>>>>>> What exactly are you showing us here?
>>>>>>> 
>>>>>>> he's demonstrating a lack of reading comprehension.   I'm done. thread 
>>>>>>> on ignore.
>>>>>> I think its proof that Apple products rot your brain!
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> -- 
>>>>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>>>>> To make changes to your subscription:
>>>>>> http://www.postgresql.org/mailpref/pgsql-general
>>>>> 
>>>>> 
>>>>> 
>>>>> -- 
>>>>> David Blomstrom
>>>>> Writer & Web Designer (Mac, M$ & Linux)
>>>>> www.geobop.org
>>>> 
>>>> 
>>>> 
>>>> -- 
>>>> Melvin Davidson
>>>> I reserve the right to fantasize.  Whether or not you 
>>>> wish to share my fantasy is entirely up to you. 
>>> 
>>> 
>>> 
>>> -- 
>>> David Blomstrom
>>> Writer & Web Designer (Mac, M$ & Linux)
>>> www.geobop.org
> 
> 
> 
> -- 
> David Blomstrom
> Writer & Web Designer (Mac, M$ & Linux)
> www.geobop.org

Re: [GENERAL] Importing CSV File

[Rob Sargent]

On 10/27/2015 10:04 AM, Adrian Klaver wrote:

On 10/27/2015 08:44 AM, Jeff Janes wrote:

On Mon, Oct 26, 2015 at 2:45 PM, David Blomstrom
mailto:david.blomst...@gmail.com>> wrote:

I tried to import a CSV file into a PostgreSQL table using pgAdmin
III. I got an error message: "extra data after last column."


All my spreadsheets have an "end of data" column that has /r/n in
each cell. When I import a CSV file into a MySQL table, everything
beyond /r/n is ignored. Is there some way to tell PostgreSQL to stop
at /r/n?


How does it know when to stop ignoring and start the next record?


I wondered about that also. I did find this:

http://dev.mysql.com/doc/refman/5.7/en/load-data.html

LINES TERMINATED BY



You could write a little awk or perl script to give the PROGRAM option
of copy, but you can't do that within pgAdmin.

Cheers,

Jeff



Whence the csv file? If it starts out in spreadsheet, can you not export 
only the columns you want in the database?


Google "postgres import tool" finds several options

Re: [GENERAL] Domain check constraint not honored?

[Rob Sargent]

On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote:
I have created a custom type as a domain based on text, which adds a 
check constraint using a regexp to limit it to containing digits and 
'.'. However I am finding I can add values with other characters to a 
column of this type. Is this to be expected for some reason?


Or alternately, did I define the constraint wrong somehow? It is 
defined thus:


CREATE DOMAIN hierpath AS text
CHECK(
   VALUE ~ '[0-9.]+'
);

Checking the docs I'm pretty sure the '.' doesn't need escaping but I 
also tried a test leaving it out ( '[0-9]+') and the result is the 
same. It lets me store letters in a column defined to be of this type.


The version() function tells me
"PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 
4.8.2-19ubuntu1) 4.8.2, 64-bit"


Thanks,
Eric


I think you regexp is too weak.  So long as the value has a digit or 
period, it's good.

'^[0-9.]+$' might work

Re: [GENERAL] Domain check constraint not honored?

[Rob Sargent]

On 10/29/2015 12:29 PM, Eric Schwarzenbach wrote:


Thank you! (Slapping head)
Your regexp seems to do the trick.

On 10/29/2015 01:49 PM, Rob Sargent wrote:

On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote:
I have created a custom type as a domain based on text, which adds a 
check constraint using a regexp to limit it to containing digits and 
'.'. However I am finding I can add values with other characters to 
a column of this type. Is this to be expected for some reason?


Or alternately, did I define the constraint wrong somehow? It is 
defined thus:


CREATE DOMAIN hierpath AS text
CHECK(
   VALUE ~ '[0-9.]+'
);

Checking the docs I'm pretty sure the '.' doesn't need escaping but 
I also tried a test leaving it out ( '[0-9]+') and the result is the 
same. It lets me store letters in a column defined to be of this type.


The version() function tells me
"PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc 
(Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit"


Thanks,
Eric


I think you regexp is too weak.  So long as the value has a digit or 
period, it's good.

'^[0-9.]+$' might work



Cool.

Note that this is a bottom-post forum and unfortunately so imho. Please 
don't top post.


Also thought I should mention that there is an ip address type if that's 
what you're trying to accomplish.

Re: [GENERAL] Domain check constraint not honored?

[Rob Sargent]

On 10/29/2015 04:29 PM, Eric Schwarzenbach wrote:

On 10/29/2015 03:44 PM, Alvaro Herrera wrote:
segment needing to be filled with zeros to a fixed length.) (Also 
FWIW, the latest version of this regexp is now '^([0-9]+.)*[0-9]+$')




Cheers,

Eric



So it can start with a dot, but not end with one?


--
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] Domain check constraint not honored?

[Rob Sargent]

On 10/29/2015 05:01 PM, Tom Lane wrote:

Eric Schwarzenbach  writes:

... (Also FWIW, the latest version of
this regexp is now '^([0-9]+.)*[0-9]+$')

Um, that's not gonna do what you want at all.  Outside brackets, a dot
is a wildcard.  (Regex syntax is a mess :-(.)

regards, tom lane



arg. back to level two of regexpness for me :(  I read it as ^[0-9.]+

Re: [GENERAL] Hierarchical Query Question (PHP)

[Rob Sargent]

On 10/30/2015 04:10 PM, David Blomstrom wrote:
Just so I understand what's going on, I can create a lookup table by 
pasting this code...


create table taxon (
  taxonid serial,
  descr text
);
create table gz_life_mammals (
  id serial,
  taxonid integer, -- use the lookup table
  parentid integer -- use the lookup table
);

...into pgAdmin III, right? (I can't use the shell/terminal at the 
moment, and it will be at least a few weeks before I can get it 
fixed.) And this script will create TWO tables - gz_life_mammals and a 
matching "lookup table"?


If I'm following correctly it will create two tables, the first is the 
definition of the lookup table.  You should add an index its taxonid (if 
'serial' doesn't imply that?)


In the second table (gz_lief_mamals) have taxonid and parentid as 
"foreign keys" to taxon.taxonid.


The gui side of pgAdmin3 should help out here, but it's been quite a 
while since I was there.

Re: [GENERAL] Hierarchical Query Question (PHP)

[Rob Sargent]

On 10/30/2015 04:10 PM, David Blomstrom wrote:
Just so I understand what's going on, I can create a lookup table by 
pasting this code...


create table taxon (
  taxonid serial,
  descr text
);
create table gz_life_mammals (
  id serial,
  taxonid integer, -- use the lookup table
  parentid integer -- use the lookup table
);

...into pgAdmin III, right? (I can't use the shell/terminal at the 
moment, and it will be at least a few weeks before I can get it 
fixed.) And this script will create TWO tables - gz_life_mammals and a 
matching "lookup table"?


If I'm following correctly it will create two tables, the first is the 
definition of the lookup table.  You should add an index its taxonid (if 
'serial' doesn't imply that?)


In the second table (gz_lief_mamals) have taxonid and parentid as 
"foreign keys" to taxon.taxonid.


The gui side of pgAdmin3 should help out here, but it's been quite a 
while since I was there.

Re: [GENERAL] Hierarchical Query Question (PHP)

[Rob Sargent]

On 10/30/2015 04:38 PM, David Blomstrom wrote:

Ah, yes - "Execute SQL." It created the table this time. Awesome.

One other question - when I close the SQL window, it asks me if I want 
to save the file. Is there any special reason for saving it? It looks 
like it simply saved a copy of the query I executed.


On Fri, Oct 30, 2015 at 3:36 PM, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 10/30/2015 03:29 PM, David Blomstrom wrote:

Thanks for the tips.

In pgAdmin III, I can create a table step-by-step by choosing
Edit >
Object > New Table

But is there a pace for me to past in a block of code that
creates the
table with just one process? If I click on the SQL icon, a SQL
window
opens up, and I can past the code into SQL Editor, but I don't
know how
to "activate" it; pressing ENTER does nothing.


http://pgadmin.org/docs/1.20/query.html

The green arrow, menu Query --> Execute or F5, take your pick.



I'm following a pgAdmin tutorial, but this page appears to
focus on the
terminal instead...

http://pgadmin.org/docs/1.4/pg/tutorial-table.html



-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org 
This goes back to the issue of "scripting".  Must of us would rather 
save the sql in a file and have it to re-run, edit, re-run as needed.  
Then add it version control.  But that takes a working terminal.

Re: [GENERAL] Hierarchical Query Question (PHP)

[Rob Sargent]

On 10/30/2015 04:55 PM, David Blomstrom wrote:
The field descr would presumably hold the values I originally had in 
the field Taxon - e.g. the names of various taxons, like 'Mammalia' 
and 'Canis-lupus.' The field id is just a numerical key, and I already 
have the numerical values for parentid.


But what am I supposed to put in the field taxonid?
Hm,  you might want to take a  step back.  What does the parentid field 
you already have represent.  Is it you mysql version of the data.  Does 
it point to an id of a taxon from the version?  If so you need to 
redefine the taxon table to just integer, not serial and load that table 
from you csv dump of mysql.


Otherwise I suspect you will need redo your parentids.

Re: [GENERAL] Hierarchical Query Question (PHP)

[Rob Sargent]

On 10/30/2015 04:55 PM, David Blomstrom wrote:
The field descr would presumably hold the values I originally had in 
the field Taxon - e.g. the names of various taxons, like 'Mammalia' 
and 'Canis-lupus.' The field id is just a numerical key, and I already 
have the numerical values for parentid.


But what am I supposed to put in the field taxonid?
I'm going to shut up - because the OP might be better served by a single 
voice and other on this thread are surely more capable.

Re: [GENERAL] Taking lot time

[Rob Sargent]

On 11/03/2015 07:42 AM, Ramesh T wrote:

I have a Query it taking a lot of time to fetch results
so,explain query gave

"Hash Join  (cost=55078.00..202405.95 rows=728275 width=418)"
"  Hash Cond: (itd.tran_id = iad._adj__id)"
"  ->  Seq Scan on inv_detail itd  (cost=0.00..40784.18 rows=731029 
width=95)"

"Filter: (event_type = ANY ('{21,22,3,5}'::integer[]))"
"  ->  Hash  (cost=20590.78..20590.78 rows=610978 width=331)"
"->  Seq Scan on inv_adj  iad  (cost=0.00..20590.78 
rows=610978 width=331)"


Can you Please let me know wt happen in query..?wt should i do..
I could be way off base (again) but I think the first thing you do is 
supply the query (sql).

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

On 11/04/2015 03:03 AM, Achilleas Mantzios wrote:
Sorry for being kind of late to the party (I was in 2015.PgConf.EU 
!!), and not having read
most of the replies, what we have been successfully doing for this 
problem for our app

is do it this way :
parents int[] -- where parents stores the path from the node to the 
root of the tree

and then have those indexes :
btree (first(parents))
btree (level(parents)) -- length
btree (last(parents))
gin (parents gin__int_ops) -- the most important

This has been described as "genealogical tree" approach, and works 
very good, IMHO much better

than nested sets.

Is there a more complete description of this approach available?  By the 
title one might assume could be applied to populations as opposed to 
phylogeny (the OP's use case).  Does it deal with consanguinity?  Does 
it perform well going "up" the tree (which is of course branched at 
every level)?

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

On 11/05/2015 04:56 AM, Achilleas Mantzios wrote:

On 04/11/2015 17:53, Rob Sargent wrote:

On 11/04/2015 03:03 AM, Achilleas Mantzios wrote:
Sorry for being kind of late to the party (I was in 2015.PgConf.EU 
!!), and not having read
most of the replies, what we have been successfully doing for this 
problem for our app

is do it this way :
parents int[] -- where parents stores the path from the node to the 
root of the tree

and then have those indexes :
btree (first(parents))
btree (level(parents)) -- length
btree (last(parents))
gin (parents gin__int_ops) -- the most important

This has been described as "genealogical tree" approach, and works 
very good, IMHO much better

than nested sets.

Is there a more complete description of this approach available?  By 
the title one might assume could be applied to populations as opposed 
to phylogeny (the OP's use case).  Does it deal with consanguinity?  
Does it perform well going "up" the tree (which is of course branched 
at every level)?


From here https://en.wikipedia.org/wiki/Phylogenetic_tree I assume 
that phylogenetic trees are normal
trees, and I see no reason why not be modeled with the genealogical 
approach described. The earliest paper

I based my work on was :
https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s&url=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps&usg=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg&sig2=I9yC_tpyeWrEueDJTXbyAA&bvm=bv.106674449,d.d24&cad=rja

Finding the root is O(1). Going "up" the tree or finding common 
ancestry is reduced to the problem
of finding overlap/intersections/contains/contained between postgresql 
arrays.


The indexes, functions and operators provided by contrib/intarray were 
a basic element for the success of this

approach.

Going "up" a genealogy to me means getting two parents, four 
grandparents, 8 great grandparents etc.  On a good day, at least when 
there are no loops.  This isn't, to my understanding, how phylogeny 
works (but my genetics degree was thirty year ago) so perhaps I'm still 
confused by the titles used.  And certainly not to say that your 
approach isn't what the OP really needs!

Re: [GENERAL] Recursive Arrays 101

[Rob Sargent]

On 11/05/2015 11:08 AM, Gavin Flower wrote:

On 06/11/15 04:33, Rob Sargent wrote:

On 11/05/2015 04:56 AM, Achilleas Mantzios wrote:

On 04/11/2015 17:53, Rob Sargent wrote:

On 11/04/2015 03:03 AM, Achilleas Mantzios wrote:
Sorry for being kind of late to the party (I was in 2015.PgConf.EU 
!!), and not having read
most of the replies, what we have been successfully doing for this 
problem for our app

is do it this way :
parents int[] -- where parents stores the path from the node to 
the root of the tree

and then have those indexes :
btree (first(parents))
btree (level(parents)) -- length
btree (last(parents))
gin (parents gin__int_ops) -- the most important

This has been described as "genealogical tree" approach, and works 
very good, IMHO much better

than nested sets.

Is there a more complete description of this approach available?  
By the title one might assume could be applied to populations as 
opposed to phylogeny (the OP's use case). Does it deal with 
consanguinity?  Does it perform well going "up" the tree (which is 
of course branched at every level)?


From here https://en.wikipedia.org/wiki/Phylogenetic_tree I assume 
that phylogenetic trees are normal
trees, and I see no reason why not be modeled with the genealogical 
approach described. The earliest paper

I based my work on was :
https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CCUQFjABahUKEwiR6auUlvnIAhXGvhQKHVyDA-s&url=https%3A%2F%2Fdownload.samba.org%2Fpub%2Funpacked%2Fldb%2Fldb_sqlite3%2Ftrees.ps&usg=AFQjCNEktJsibP435MBki5cdGmO_CzKmwg&sig2=I9yC_tpyeWrEueDJTXbyAA&bvm=bv.106674449,d.d24&cad=rja 



Finding the root is O(1). Going "up" the tree or finding common 
ancestry is reduced to the problem
of finding overlap/intersections/contains/contained between 
postgresql arrays.


The indexes, functions and operators provided by contrib/intarray 
were a basic element for the success of this

approach.

Going "up" a genealogy to me means getting two parents, four 
grandparents, 8 great grandparents etc.  On a good day, at least when 
there are no loops.  This isn't, to my understanding, how phylogeny 
works (but my genetics degree was thirty year ago) so perhaps I'm 
still confused by the titles used.  And certainly not to say that 
your approach isn't what the OP really needs!



You're actually going 'DOWN' the tree, in terms of how trees are used 
in computer science & graph theory!


See http://www.mathcove.net/petersen/lessons/get-lesson?les=32


Cheers,
Gavin



Fine.  Be that way :)  Still the question of loops/consanguinity?





--
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] Automate copy - Postgres 9.2

[Rob Sargent]

> On Jun 8, 2016, at 8:04 PM, Patrick B  wrote:
> 
> 
> 
> 2016-06-09 13:58 GMT+12:00 John R Pierce  >:
> On 6/8/2016 6:47 PM, Patrick B wrote:
> 
> 21 is the number of IDS that I wanna perform that COPY command
> 
> that didn't answer my question.  if you call your function like SELECT 
> myfunction(21);   as you showed, where are those 21 ID's coming from?
> 
> 
> I don't know what u need, mate:
> 
> 
> CREATE or REPLACE FUNCTION function(integer)
> RETURNS void AS $$
> 
> declare
>crtRow record;
> begin
>   FOR crtRow in EXECUTE 'select DISTINCT(account_id) from backup_table 
> WHERE migrated = 1 AND account_id IN '|| $1
> 
>   LOOP
> COPY
> (SELECT * FROM backup_table WHERE migrated = 1 AND account_id = 
> crtRow.account_id)
> TO '/var/lib/pgsql/' || crtrow.account_id || '.csv';
> end loop;
> end
> 
> $$ language 'plpgsql';
> 
> 
> Isn't this what u wanna know?
> select DISTINCT(account_id) from backup_table WHERE migrated = 1

So you want to call your function in a loop, or drop the passed in id and just 
do all the account at once, take your pick.

Re: [GENERAL] How to pass jsonb and inet arguments to a stored function with JDBC?

[Rob Sargent]

> On Jun 14, 2016, at 7:33 AM, Alexander Farber  
> wrote:
> 
> Dear PostgreSQL users,
> 
> I have a stored procedure defined as:
> 
> CREATE OR REPLACE FUNCTION words_merge_users(
> IN in_users jsonb,
> IN in_ip inet,
> OUT out_uid integer)
> RETURNS integer AS
> $func$
> ...
> $func$ LANGUAGE plpgsql;
> 
> Which I can successfully call at psql 9.5.3 prompt:
> 
> # SELECT out_uid FROM 
> words_merge_users('[{"sid":"abcde","auth":"","social":2,"given":"Abcde2","female":0,"stamp":1450102880}]'::jsonb,
>  '0.0.0.0'::inet);
> 
>  out_uid
> -
>1
> (1 row)
> 
> However an SQLException is thrown, when trying to call the same function via 
> JDBC driver 9.4.1208.jre7:
> 
> ERROR: function words_merge_users(character varying, character varying) does 
> not exist   
> Hint: No function matches the given name and argument types. You might need 
> to add explicit type casts.
> 
> Being a JDBC newbie I wonder, how to perform the call properly in Java?
> 
> PreparedStatement st = mDatabase.prepareStatement("SELECT out_uid 
> FROM words_merge_users(?, ?)");
> 
> String str1 = JSON.toString(users); 
> String str2 = 
> mSession.getRemoteAddress().getAddress().getHostAddress(); 
> 
> st.setString(1, str1); // 
> {"social":1,"auth":"","action":"login","users":[{"given":"alex","social":1,"auth":"","sid":"12345"}],"sid":"12345"}
> st.setString(2, str2); // "127.0.0.1"
> 
> ResultSet rs = st.executeQuery();
> while (rs.next()) {
> System.out.println(rs.getString(1));
> }
> 
> rs.close();
> st.close();
> 
> Thank you for any hints
> Alex

Probably a permission or ownership issue. Are you using same credentials for 
jdbc and psql?

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

Re: [GENERAL] Stored procedure version control

[Rob Sargent]

On 07/01/2016 06:17 PM, Jim Nasby wrote:

On 6/30/16 9:16 AM, Merlin Moncure wrote:

It's not really necessary to create version down scripts.  In five
years of managing complex database environments we've never had to
roll a version back and likely never will; in the event of a disaster
it's probably better to restore from backup anyways.


I'm surprised no one has mentioned http://sqitch.org. It makes it very 
easy to manage migrations, as well as creating downgrade scripts (if 
you use rework, and put each object into it's own file).


I do agree that down scripts are pretty over-rated as long as you have 
good test practices (as in, database unit tests). In 9 years in an 
environment where downtime was 6 figures per hour I only had 1 or 2 
deployments that had problems, and never bad enough to consider 
reverting.
I've found down scripts quite useful in development.  One does have to 
know how to neuter them on the way to production however.






--
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_dump fundenental question

[Rob Sargent]

On 07/05/2016 10:54 AM, David G. Johnston wrote:
On Tue, Jul 5, 2016 at 10:54 AM, J. Cassidy >wrote:


Hello all,

I have hopefully an "easy" question.

If I issue the pg_dump command with no switches or options i.e.

/usr/local/pgsql/bin/pg_dump -v dbabcd > /data3TB/Backup/dbabcd

Is their any "default" compression involved or not? Does pg_dump
talk to zlib during the dump process given that I have not
specified any compression on the
command line? (see above).

​
​ In general the easiest way to answer the question of whether 
something (well, a text file at least) is compressed or not is to 
attempt to open it in a text editor - if you can read the contents​

​ its not compressed.

Reading the Description section at the following location provides the 
answer you seek - no, there is no compression by default.​


​https://www.postgresql.org/docs/9.5/static/app-pgdump.html
​David J.

I like the 'file' command, and now the MS is shipping bash, it should be 
available to all.

Re: [GENERAL] pasting a lot of commands to psql

[Rob Sargent]

If, on the wild chance you're an emacs user, if the section is in a 
".sql" buffer Ctrl-C Ctrl-B will send the entire buffer.  Not sure if 
there's a practical limit on the number of line/statements, but I've 
used this with a couple of "pages" worth of sql.


On 07/07/2016 11:48 AM, Dmitry Shalashov wrote:

Oh sweet! Thanks!


Dmitry Shalashov, surfingbird.ru  & relap.io 



2016-07-07 20:39 GMT+03:00 Scott Mead >:




On Thu, Jul 7, 2016 at 1:30 PM, Dmitry Shalashov
mailto:skau...@gmail.com>> wrote:

Hi Sameer,

I am trying to copy-paste (and execute) random snippets of SQL
to psql console.

There is another ways to do it, which do not involve
copy-paste, but I am wondering why is copy-paste won't work.
What exactly is happening there...


Dmitry Shalashov, surfingbird.ru  &
relap.io 

2016-07-07 20:26 GMT+03:00 Sameer Kumar
mailto:sameer.ku...@ashnik.com>>:



On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov,
mailto:skau...@gmail.com>> wrote:

Hi everyone.

Let say that I have some sql file with like hundred of
simple statements in it. I `cat` it, copy it to
buffer, go to my beloved psql and insert it there.
But somewhere after first few lines it screws over:

b2b=> BEGIN;
b2b=> INSERT INTO oko_topsites
VALUES('russian_federation','ati.su
',0,NULL,5);
INSERT INTO oko_topsites
VALUES('russian_federation'b2b=> INSERT INTO
oko_topsites VALUES('russian_federation','atn.ua
',0,NULL,5);
b2b=> INSERT INTO oko_topsites
VALUES('russian_federation','audit-it.ru
',0,NULL,5);
b2b=> INSERT INTO oko_topsites
VALUES('russian_federation','aup.ru
',0,NULL,5);
'
b2b=> INSERT INTO oko_topsites
VALUES('russian_federation','autocentre.ua
',0,NULL,5);

b2b=> INSERT INTO oko_topsites
VALUES('russian_federation','autodoc.ru
',0,NULL,5);

b2b=> INSERT INTO oko_topsites
VALUES('russian_federation','calend.ru '

b2b(> INSERT INTO oko_topsites
VALUES('russian_federation','calorizator.ru
',0,NULL,5)

Unclosed quotes, unclosed parenthesis - anyway it wont
work.

How to safely insert big number of statements to psql
at once?
I am aware about "execute this file" \i option of
psql, that is not the answer I am looking for, thanks :-)


My personal favorite for this exact thing is to use '\e'

  When you are in psql, if you \e (on *nix) it will open a temp
file in whatever your $EDITOR variable is set ( I use vim).  Paste
your data, then save-close the file.  It will put you back into
psql and execute the command for you.

--Scott


What are you exactly aiming to do?

Have you tried -
psql  < myfile



Dmitry Shalashov, surfingbird.ru
 & relap.io 

-- 
--

Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com





-- 
--

Scott Mead
Sr. Architect
/OpenSCG /
http://openscg.com

Re: [GENERAL] Detecting if current transaction is modifying the database

[Rob Sargent]

On 08/05/2016 01:48 PM, Christian Ohler wrote:
Thanks, fair point.  I should have mentioned that I know about 
triggers but was hoping to find a less invasive mechanism (IIUC, I'd 
have to install a trigger on every table) – it seems to me that 
Postgres should just be able to tell me whether COMMIT will do 
anything, it obviously has to track that somehow (or some 
approximation of it).


Another thing I should have mentioned is that I don't consider 
incrementing a sequence to be a modification.



On Fri, Aug 5, 2016 at 12:35 PM, Alex Ignatov 
mailto:a.igna...@postgrespro.ru>> wrote:


Hi! Make trigger function

Alex Ignatov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company




On Fri, Aug 5, 2016 at 10:25 PM +0300, "Christian Ohler"
mailto:oh...@shift.com>> wrote:

Hi,

I'm trying to find a way to have Postgres tell me if the
current transaction would modify database if I committed it
now.  I can live with a conservative approximation (sometimes
– ideally, rarely – get a "yes" even though nothing would be
modified, but never get a "no" even though there are pending
modifications).  It's acceptable (probably even desirable) if
a no-op write operation like "UPDATE foo SET bar = 1 WHERE bar
= 1" is considered a modification.

(The use case is an audit log mechanism vaguely similar to
pgMemento.)


This sentence from
https://www.postgresql.org/docs/9.5/static/view-pg-locks.html
 :

> If a permanent ID is assigned to the transaction (which
normally happens
> only if the transaction changes the state of the database),
it also holds
> an exclusive lock on its permanent transaction ID until it ends.

makes me think that I can perhaps do it as follows:

SELECT count(*) FROM pg_locks WHERE pid=pg_backend_pid() AND
locktype='transactionid' AND mode='ExclusiveLock' AND granted;

Is that right?  "Permanent transaction ID" refers to the XID,
correct?  Are there other, better ways?  Are there ways to
avoid false positives due to temp tables?

Thanks in advance,
Christian.


What sort of interface are you looking for.  Where/When would you grab 
the information? Do what with it?  Log triggers are the typical pattern 
here (with packages just for that sort of thing).

Re: [GENERAL] Detecting if current transaction is modifying the database

[Rob Sargent]

On 08/05/2016 02:15 PM, Christian Ohler wrote:

On Fri, Aug 5, 2016 at 12:55 PM, Rob Sargent  wrote:

What sort of interface are you looking for.  Where/When would you grab the 
information? Do what with it?  Log triggers are the typical pattern here (with 
packages just for that sort of thing).

I'm looking for a statement (or sequence of statements) that, when run
within a transaction, tells the client if any writes are happening in
that transaction – basically an interface similar to my proposed
solution.  I have some database wrapper code on the client that passes
along arbitrary statements to Postgres, and I'd like for that wrapper
code to be able to determine whether the current transaction is a
write (for various purposes including logging).  It would be nice not
to make the client-side wrapper code dependent on instrumentation of
the database schema itself.

What can you tell me about my proposed solution?  Does it do what I
describe I want from it?  Are there limitations I should be aware of?
At what point do you intend to inform the client that the db will be 
(significantly) altered? You're planned call is within the transaction 
and presumably late in the sequence (so the locks have been created). 
Not sure if your client can see any results until after the transaction 
has been largely executed.  Does that matter?




--
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] Sequential vs. random values - number of pages in B-tree

[Rob Sargent]

On 08/23/2016 07:44 AM, Francisco Olarte wrote:

Hi pinker:

On Tue, Aug 23, 2016 at 2:26 PM, pinker  wrote:

I am just surprised by the order of magnitude in the difference though. 2
and 27 minutes that's the huge difference... I did another, simplified test,
to make sure there is no duplicates and the only difference between both
sets is the order:

...

INSERT INTO t_sequential SELECT * FROM source_sequential;
102258,949 ms
INSERT INTO t_random SELECT * FROM source_random;
1657575,699 ms

If I read correctly, you are getting 100s/10Mkeys=10us/key in
sequential, and 165 in random.

I'm not surprissed at all. I've got greater differences on a memory
tree, sorted insertion can be easily optimized to be very fast. AS an
example, sequential insertion can easily avoid moving data while
filling the pages and, with a little care, it can also avoid some of
them when splitting. I'm not current with the current postgres
details, but it does not surprise me they have big optimizations for
this, especially when index ordered insertion is quite common in
things like bulk loads or timestamped log lines.

Francisco Olarte.



And if each insert is in a separate transaction, does this still hold true?




--
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] Sequential vs. random values - number of pages in B-tree

[Rob Sargent]

On 08/23/2016 08:34 AM, Francisco Olarte wrote:

On Tue, Aug 23, 2016 at 4:28 PM, Rob Sargent  wrote:

On 08/23/2016 07:44 AM, Francisco Olarte wrote:

On Tue, Aug 23, 2016 at 2:26 PM, pinker  wrote:

I am just surprised by the order of magnitude in the difference though. 2
and 27 minutes that's the huge difference... I did another, simplified
test,
to make sure there is no duplicates and the only difference between both
sets is the order:

...

INSERT INTO t_sequential SELECT * FROM source_sequential;
102258,949 ms
INSERT INTO t_random SELECT * FROM source_random;
1657575,699 ms

If I read correctly, you are getting 100s/10Mkeys=10us/key in
sequential, and 165 in random.

I'm not surprissed at all. I've got greater differences on a memory
tree, sorted insertion can be easily optimized to be very fast. AS an
example, sequential insertion can easily avoid moving data while
filling the pages and, with a little care, it can also avoid some of
them when splitting. I'm not current with the current postgres
details, but it does not surprise me they have big optimizations for
this, especially when index ordered insertion is quite common in
things like bulk loads or timestamped log lines.

And if each insert is in a separate transaction, does this still hold true?

What are you referring to by 'this'? ( BTW, bear in mind one
transaction needs at least a disk flush, and, if done via network, at
least one RTT, so I doubt you can achieve 10us/transaction unless you
have very special conditions ).

Francisco Olarte.
By 'this' I was referring to the optimizations mentioned, and am 
wondering if this holds true under user load.  Much magic can happen in 
a custom data load, but do these optimization apply to an application 
loading single (or perhaps several) records per transaction.  Does one, 
in that scenario, not suffer any consequence for continuously loading 
one side of the tree (the rightmost node?).



--
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] Request to share information regarding postgresql pg_xlog file.

[Rob Sargent]

> On Sep 15, 2016, at 1:20 AM, Yogesh Sharma 
>  wrote:
> 
> Dear John and all,
>  
> >8.1 has been obsolete and unsupported for about 6 years now.8.1.18 was 
> >released in 2009, the final 8.1.23 release was in 2010, after which it was 
> >>dropped.
> Yes, we understood your point.
> But we require some information related to this rpm.
>  
> >These errors suggest disk file corruption, this can occur from unreliable 
> >storage, undetected memory errors, and other such things.
> How we can verify what is actual problem in system?
>  
> Also please share some information related to below.
> we tried to stop the postgresql but it couldn’t stop and timout after 60 sec.
> please confirm below message in postgre logs.
> FATAL:  terminating connection due to administrator command
>  
>  
> Regards,
> Yogesh
>  
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
> Sent: Thursday, September 15, 2016 11:28 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Request to share information regarding postgresql 
> pg_xlog file.
>  
> On 9/14/2016 10:09 PM, Yogesh Sharma wrote:
> Thanks for your support and suggestion.
> We are using below postgresql rpm.
> postgresql-8.1.18-2.1
> 
> thats not the full RPM name, thats just the version.
> 
> 8.1 has been obsolete and unsupported for about 6 years now.8.1.18 was 
> released in 2009, the final 8.1.23 release was in 2010, after which it was 
> dropped.
> 
> current releases are 9.1 (soon to be obsoletted), 9.2, 9.3, 9.4, and 9.5, 
> with 9.6 in release candidate state.
> 
> 
> CONTEXT:  writing block 53 of relation 1663/16385/280951
> ERROR:  could not open relation 1663/16385/280951: No such file or directory
> 
> 
> These errors suggest disk file corruption, this can occur from unreliable 
> storage, undetected memory errors, and other such things.
> 
> 
> 
>  
> 
> -- 
> john r pierce, recycling bits in santa cruz
What operating system is this running on? John is most likely correct: the disk 
is not healthy.  How you deal with that depends on your OS
Are you looking for the rpm for that version? Or do you have some other reason 
for asking about the rpm versus questions about the postgres version
This list requests that you “bottom post” i.e. add your comments to the bottom, 
not the top. (I don’t like it, but that’s the protocol here)

Re: [GENERAL] Chante domain type - Postgres 9.2

[Rob Sargent]

On 09/26/2016 08:14 AM, Adrian Klaver wrote:

On 09/26/2016 06:54 AM, Thomas Kellerer wrote:

Rakesh Kumar schrieb am 26.09.2016 um 15:08:
You sound like you think that varchar(50) is somehow cheaper than 
text.


The biggest impediment to text cols in other RDBMS  is no index 
allowed.
If PG has an elegant solution to that, then yes I see the point made 
by the

original poster.


Don't confuse Postgres' "text" data type with "text" in other DBMS.


Just be aware that layers above the database often do not understand 
that and will see text as a memo field. For instance in Django a text 
field will get rendered as a Textarea widget whereas a varchar field 
will be rendered as an TextInput widget. You can override that, but it 
is extra work. Luckily Postgres has the notion of an unbounded varchar:


https://www.postgresql.org/docs/9.5/static/datatype-character.html

".. If character varying is used without length specifier, the type 
accepts strings of any size. The latter is a PostgreSQL extension."


This allows you to have 'text' without it being called text, as stated 
below.




There is no difference whatsoever between varchar and text in Postgres.











Does that trick remove the overhead (length check) Tom mentioned upstream?




--
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] Incrementally refreshed materialized view

[Rob Sargent]

Of course 9.5 is the current release so the answer is Yes, since 9.5

On 09/26/2016 12:29 PM, Rakesh Kumar wrote:


*Does PG support INCREMENTAL MV ? Looks like not (until 9.5)*

Re: [GENERAL] Query help

[Rob Sargent]

> On Oct 4, 2016, at 9:31 PM, Bret Stern  
> wrote:
> 
> Good evening,
> I'm curious about a way to ask the following question of my vendors
> table.
> 
> psuedo1 "select all vendors which exist in BUR and EBC and SNJ"
> 
> and
> psuedo2 "select all vendors which DO NOT exist in all three show rooms
> 
> 
> The data is from a Sage accounting system which I pull out and place in a
> pg db. What we are trying to do is identify which vendors are defined in
> all three of our showrooms, and vice-versa, which ones are not.
> 
> ap_vendors table
>   company_code character varying(10) NOT NULL,
>   ap_division_no character varying(2) NOT NULL,
>   vendor_no character varying(7) NOT NULL,
>   terms_code character varying(2),
>   vendor_name character varying(30),
>   address_line1 character varying(30),
>   address_line2 character varying(30),
>   address_line3 character varying(30),
>   city character varying(20),
>   state character varying(2),
>   zip_code character varying(10),
>   telephone_no character varying(17),
>   fax_no character varying(17),
>   CONSTRAINT ap_vendors_pk PRIMARY KEY (company_code, ap_division_no, 
> vendor_no)
> 
> sample records:
> "BUR";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''"
> "BUR";"00";"AGORA";"30";"Agora Natural 
> Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
> "BUR";"00";"AKDOP";"30";"AKDO 
> Pacific";"''";"''";"''";"''";"''";"94545";"''";"''"
> "EBC";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''"
> "EBC";"00";"AGORA";"30";"Agora Natural 
> Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
> "EBC";"00";"ARIZ01";"30";"Arizona 
> Tile";"''";"''";"''";"''";"''";"94550";"''";"''"
> "SNJ";"00";"AKDOP";"30";"AKDO 
> Pacific";"''";"''";"''";"''";"''";"94545";"''";"''"
> "SNJ";"00";"AGORA";"30";"Agora Natural 
> Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
> "SNJ";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''"
> 
> What I need is a query which I can modify to return only vendors which exists
> in all three company_code columns ( BUR, EBC, SNJ) (they represent showroom 
> location)
> 
> eg; exists in BUR, EBC, SNJ
> ADEXU
> AGORA
> 
> OR
> 
> modify the query to return only the vendors which DO NOT exist in all
> three showrooms based on the first column company_code
> 
> eg; 
> AKDOP only exists in BUR and SNJ
> ARIZ01only exists in EBC
> 
> Thanks
> Bret
> 
> 
Not sure I like the schema but 
select vendor_no, count(*) from ap_vendors having count(*) = 3;

and maybe count(*) < 3 is your second answer.



-- 
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] high transaction rate

[Rob Sargent]

2.  Accumulation of dead tuples leading to what should be very short 
operations taking longer.


No idea of that is helpful but where I would probably start



Please tell me that in this case, updating 2 (big)integer columns does 
not generate dead tuples (i.e. does not involve a insert/delete pair).

Re: [GENERAL] FreeBSD 10 => 11: Dump and reload your PostgreSQL database unless you like it broken

[Rob Sargent]

On 12/07/2016 02:06 PM, Kevin Grittner wrote:

On Wed, Dec 7, 2016 at 7:33 AM, Michael Sheaver  wrote:

I would like to echo the sentiment on collation and expand it to
character sets in general. When issues with them come up, they do
take an incredible amount of time and effort to resolve, and are
one of my own biggest pain points when dealing with databases and
datasets from other sources.

I would be much happier if most developers understood the
difference between a character set (e.g., Unicode) and a character
encoding scheme (e.g., UTF-8 or UTF-16) and how the two concepts
relate.  If we reached a point where most DBAs understood the point
of being able to set a client_encoding that is different from the
server_encoding, I think I would need to pop the cork on some
champagne.

Hm.  Maybe a topic for a blog post

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



+1 - Confuses me every time


--
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] high transaction rate

[Rob Sargent]

On 12/07/2016 09:58 AM, John R Pierce wrote:

On 12/7/2016 8:47 AM, Rob Sargent wrote:
Please tell me that in this case, updating 2 (big)integer columns 
does not generate dead tuples (i.e. does not involve a insert/delete 
pair).


if the fields being updated aren't indexed, and there's free tuple 
space that has already been vacuumed in the same block, then the 
update is done via "HOT" (or is it HEAT?) within the same block... but 
with 1000s of updates per second to the same 500 rows ?  odds of 
autovacuum keeping up are sketchy..otherwise, all updates are 
insert/delete operations due to the requirements of MVCC

How does your reply change, if at all, if:
 - Fields not index
 - 5000 hot records per 100K records (millions of records total)
 - A dozen machines writing 1 update per 10 seconds (one machine 
writing every 2 mins)

 - - each to a different "5000"
  or (two modes of operation)
 - - each to same "5000"

My guess this would be slow enough even in the second mode?  Or at this 
rate and style should I care?

Sorry for taking this off from OP's point




--
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] high transaction rate

[Rob Sargent]

On 12/07/2016 03:32 PM, John R Pierce wrote:

On 12/7/2016 2:23 PM, Rob Sargent wrote:

How does your reply change, if at all, if:
 - Fields not index
 - 5000 hot records per 100K records (millions of records total)
 - A dozen machines writing 1 update per 10 seconds (one machine 
writing every 2 mins)

 - - each to a different "5000"
  or (two modes of operation)
 - - each to same "5000"

My guess this would be slow enough even in the second mode?  Or at 
this rate and style should I care?
Sorry for taking this off from OP's point 


thats 1 update of 5000 records every 2 minutes per each of 12 client 
hosts?   thats still a fair amount of tuples/second and in a table 
with millions of records, the vacuum will have a lot more to go through.


9.6 has some potentially significant enhancements in how autovacuum 
operates with respect to incrementally freezing blocks.



if you think your update patterns can take advantage of HOT, its a 
good idea to set the FILL_FACTOR of the table prior to populating it, 
maybe to 50% ?  this will make the initial table twice as large, but 
provide freespace in every block for these in-block HOT operations.


for a table that large, you'll definitely need to crank up the 
aggressiveness of autovacuum if you hope to keep up with that number 
of stale tuples distributed among millions of records.




Much appreciated - endOfOffTopic :)


--
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] huge table occupation after updates

[Rob Sargent]

> On Dec 10, 2016, at 6:25 AM, Tom DalPozzo  wrote:
> 
> Hi,
> you're right, VACUUM FULL  recovered the space, completely.
> So, at this point I'm worried about my needs.
> I cannot issue vacuum full as I read it locks the table.
> In my DB, I (would) need to have a table with one bigint id field+ 10 bytea 
> fields, 100 bytes long each (more or less, not fixed). 
> 5/1 rows maximum, but let's say 5000.
> As traffic I can suppose 1 updates per row per day (spread over groups of 
> hours; each update involving two of those fields, randomly. 
> Also rows are chosen randomly (in my test I used a block of 2000 just to try 
> one possibility).
> So, it's a total of 50 millions updates per day, hence (50millions * 100 
> bytes *2 fields updated) 10Gbytes net per day.
> I'm afraid it's not possible, according to my results.
> Reagrds
> Pupillo
> 

Are each of the updates visible to a user or read/analyzed by another activity? 
 If not you can do most of the update in memory and flush a snapshot 
periodically to the database.



-- 
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] huge table occupation after updates

[Rob Sargent]

> On Dec 10, 2016, at 7:27 AM, Tom DalPozzo  wrote:
> 
> Hi,
> I'd like to do that! But my DB must be crash proof! Very high reliability is 
> a must.
> I also use sycn replication.
> Regards
> Pupillo
> 
> 
> 
> 
> Are each of the updates visible to a user or read/analyzed by another 
> activity?  If not you can do most of the update in memory and flush a 
> snapshot periodically to the database.
> 
> 

This list discourages top posting. You’re asked to place your reply at the 
bottom

You haven’t laid out you’re application architecture (how many clients, who is 
reading who is writing, etc). Caching doesn’t mean your database is any less 
crash proof.  At that rate of activity, depending on architecture, you could 
lose updates in all sorts of crash scenarios.

-- 
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] huge table occupation after updates

[Rob Sargent]

> On Dec 10, 2016, at 10:01 AM, Tom DalPozzo  wrote:
> 
> 2016-12-10 16:36 GMT+01:00 Rob Sargent :
> 
> > On Dec 10, 2016, at 7:27 AM, Tom DalPozzo  wrote:
> >
> > Hi,
> > I'd like to do that! But my DB must be crash proof! Very high reliability 
> > is a must.
> > I also use sycn replication.
> > Regards
> > Pupillo
> >
> >
> >
> >
> > Are each of the updates visible to a user or read/analyzed by another 
> > activity?  If not you can do most of the update in memory and flush a 
> > snapshot periodically to the database.
> >
> >
> 
> This list discourages top posting. You’re asked to place your reply at the 
> bottom
> 
> You haven’t laid out you’re application architecture (how many clients, who 
> is reading who is writing, etc). Caching doesn’t mean your database is any 
> less crash proof.  At that rate of activity, depending on architecture, you 
> could lose updates in all sorts of crash scenarios.
> 
> ​As for crash proof, I meant that once my client app is told that her update 
> request was committed, it mustn't get lost (hdd failure apart of course). And 
> I can't wait to flush the cache before telling to the app :"committed".
> ​I can replicate also the cache on the standby PC of course. ​ 
> Regards
> Pupillo
> 
> 
> 
> ​ 
> 

OK clientA sends an update; you commit and tell clientA committed. clientB 
updates same record; Do you tell clientA of clientB’s update?
Are the two updates cumulative or destructive.  
Can you report all updates done by clientA?

-- 
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] About the MONEY type

[Rob Sargent]

> On Dec 18, 2016, at 5:23 PM, Gavin Flower  
> wrote:
> 
> On 18/12/16 12:25, Bruce Momjian wrote:
>> On Wed, Nov 30, 2016 at 01:35:12PM -0800, John R Pierce wrote:
>>> note, btw, TIMESTAMP WITH TIME ZONE doesn't actually store the timezone...
>>> rather, it converts it to an internal representation of GMT, and then 
>>> converts
>>> it back to display time at the client's current (or specified) time zone.
>> Right, TIMESTAMP WITH TIME ZONE converts the timestamp value to the
>> local time zone on output.  Imagine a monetary type that converted the
>> money amount to local currency on output --- that would be cool.
>> 
> Hmm...
> 
> Would need to know the appropriate conversion rate. the 2 obvious 
> dates/times, on entry and now, may neither be the one wanted.
> 
> Also, often the buy/sell conversion rates are not the same!
> 
> Am sure there also other issues.
> 
> I don't think automatic conversion is as easy as you make it out to be.
> 
> 
> 
> Cheers,
> 
> Gavin
> 
Bets on how serious Mr. Pierce was are currently trending in Vegas.



-- 
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

[Rob Sargent]

I would hope Postgres core folk take no more than a nanosecond to reject the 
idea that they work on an IDE. Focus on reading and writing faster and faster 
ACID all the while. 

> On Dec 29, 2016, at 5:32 PM, Tim Uckun  wrote:
> 
> Honestly I don't even like JS. Having said that I am not too crazy about 
> PL-PGSQL either. I am willing to put up with either given that they are 
> supported widely in default installs of postgres in AWS, Linux and MacOSX,
> 
> As I said before, I think posgres gives a unique and underutilized language 
> platform. You can code in different languages, it has a good variety of built 
> in types, and of course you get persistance and caching built in!  Using 
> DBLINK you might even be able to separate out your code from the bulk of your 
> data in another database. Postgres all the way down!
> 
> It's fun to play around with.  There is a lot of missing pieces though. A 
> good IDE like thing would be good, version control would be nice, deeper 
> namespacing (hierarchical schemas?), easier testing etc would go a long way. 
> 
> Thanks for all the input guys! 
> 
>> On Fri, Dec 30, 2016 at 12:14 AM, Ivan Sergio Borgonovo 
>>  wrote:
>> On 12/29/2016 10:35 AM, Pavel Stehule wrote:
>> 
>>> 2016-12-29 10:03 GMT+01:00 Tim Uckun >> >:
>>> 
>>> I think it's awesome that postgres allows you to code in different
>>> languages like this. It really is a unique development environment
>>> and one that is overlooked as a development platform.  It would be
>>> nice if more languages were delivered in the default package
>>> especially lua, V8 and mruby.
>>> 
>>> 
>>> It is about dependencies and maintenance. There are not too much people
>>> who has good experience with C embedding Lua, V8 and others. Any people
>>> who can do some work are welcome.
>>> 
>>> The living outside main package has disadvantages - only enthusiast
>>> knows about it, but some advantages too - you are not fixed on
>>> PostgreSQL development cycle, and development can be faster.
>> 
>> I'll add my 2 cents.
>> 
>> Postgresql and in general SQL are about integrity and coherency.
>> Checking coherency is much easier with strict data type.
>> PL/PGSQL gives you that, JS is far far away from that.
>> 
>> Postgresql is a very flexible database and you can stretch it to do "MEAN 
>> like"[1] stuff but that's going to increase your "impedance mismatch".
>> 
>> If you think there is some space for JS in your application stack that's 
>> nearer to the client rather than to the DB.
>> Or possibly you need to do "MEAN like" stuff but you don't want to install 
>> another "database".
>> 
>> As other said using stored procedures is a two edged sword.
>> It can decouple DB schema from the application or it can increase the 
>> coupling.
>> Choosing JS for performance in the stored procedure realm is going to 
>> encourage coupling and make scalability harder and it is going to become a 
>> mess when you'll need to refactor.
>> 
>> [1] https://en.wikipedia.org/wiki/MEAN_(software_bundle)
>> 
>> -- 
>> Ivan Sergio Borgonovo
>> http://www.webthatworks.it http://www.borgonovo.net
>> 
>> 
>> 
>> 
>> -- 
>> 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] COPY: row is too big

[Rob Sargent]

> On Jan 2, 2017, at 10:13 AM, Adrian Klaver  wrote:
> 
>> On 01/02/2017 09:03 AM, vod vos wrote:
>> You know, the csv file was exported from other database of a machine, so
>> I really dont want to break it for it is a hard work. Every csv file
>> contains headers and values. If I redesign the table, then I have to cut
>> all the csv files into pieces one by one. 
> 
> If it helps:
> 
> http://csvkit.readthedocs.io/en/latest/tutorial/1_getting_started.html#csvcut-data-scalpel
>> 
>> 
>>  On 星期一, 02 一月 2017 08:21:29 -0800 *Tom Lane
>> * wrote 
>> 
>>vod vos mailto:vod...@zoho.com>> writes:
>>> When I copy data from csv file, a very long values for many
>>columns (about 1100 columns). The errors appears:
>>> ERROR: row is too big: size 11808, maximum size 8160
>> 
>>You need to rethink your table schema so you have fewer columns.
>>Perhaps you can combine some of them into arrays, for example.
>>JSON might be a useful option, too.
>> 
>>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
>> 
>> 
> 
> 
> -- 
> 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
Perhaps this is your opportunity to correct someone else's mistake. You need to 
show the table definition to convince us that it cannot be improved. That it 
may be hard work really doesn't mean it's not the right path. 

-- 
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] Index impact on update?

[Rob Sargent]

On 01/04/2017 09:59 AM, Israel Brewster wrote:

Short version:
Do indexes impact the speed of an UPDATE, even when the indexed 
columns aren't changing?


Details:
I have a table containing geographical data (Latitude, longitude, and 
elevation) with 406,833,705 records. The Latitude and Longitude 
columns are indexed. In order to better utilize the data, I've been 
looking into PostGIS, and decided I wanted to add a "Location" column 
with PostGIS type "GEOGRAPHY(point)". I then tried to populate it from 
the existing latitude/longitude data using the following query:


UPDATE data SET 
location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||' 
'||lat::text||')');


I expected this update to take quite a while, since it has 406 million 
rows to update, but at this point it's been over 19 hours since I 
started the query, and it still hasn't completed.


I'm wondering if the presence of the indexes could be slowing things 
down even though the indexed columns aren't being updated? Would I be 
better off canceling the update query, dropping the indexes, and 
trying again? Or is more likely that the update query is "almost" 
done, and it would be better to just let it run it's course? Or is 
there an even better option, such as perhaps exporting the data, 
adding the additional column in a text editor, and re-importing the 
data with a COPY command?


Thanks for any feedback/advice you can offer!
I would use a "create table redo as select *, 
location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||' 
'||lat::text||')') from original;" then index that and drop original.  
Or just "create table location as select 
location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||' 
'||lat::text||')');" along with what ever id you have for the original 
tuple (if it's not just lat+lon) and join or view as necessary after 
indexing.



--
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] Index impact on update?

[Rob Sargent]

On 01/04/2017 09:59 AM, Israel Brewster wrote:

Short version:
Do indexes impact the speed of an UPDATE, even when the indexed 
columns aren't changing?


Details:
I have a table containing geographical data (Latitude, longitude, and 
elevation) with 406,833,705 records. The Latitude and Longitude 
columns are indexed. In order to better utilize the data, I've been 
looking into PostGIS, and decided I wanted to add a "Location" column 
with PostGIS type "GEOGRAPHY(point)". I then tried to populate it from 
the existing latitude/longitude data using the following query:


UPDATE data SET 
location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||' 
'||lat::text||')');


I expected this update to take quite a while, since it has 406 million 
rows to update, but at this point it's been over 19 hours since I 
started the query, and it still hasn't completed.


I'm wondering if the presence of the indexes could be slowing things 
down even though the indexed columns aren't being updated? Would I be 
better off canceling the update query, dropping the indexes, and 
trying again? Or is more likely that the update query is "almost" 
done, and it would be better to just let it run it's course? Or is 
there an even better option, such as perhaps exporting the data, 
adding the additional column in a text editor, and re-importing the 
data with a COPY command?


Thanks for any feedback/advice you can offer!
I would use a "create table redo as select *, 
location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||' 
'||lat::text||')') from original;" then index that and drop original.  
Or just "create table location as select 
location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||' 
'||lat::text||')');" along with what ever id you have for the original 
tuple (if it's not just lat+lon) and join or view as necessary after 
indexing.



--
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] COPY: row is too big

[Rob Sargent]

On 01/05/2017 05:44 AM, vod vos wrote:

I finally figured it out as follows:

1. modified the corresponding data type of the columns to the csv file

2. if null values existed, defined the data type to varchar. The null 
values cause problem too.


so 1100 culumns work well now.

This problem wasted me three days. I have lots of csv data to COPY.


Yes, you cost yourself a lot of time by not showing the original table 
definition into which you were trying insert data.

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

[Rob Sargent]

On 01/05/2017 10:18 AM, Job wrote:

Hello guys,
a very strange thing: after upgrading from 8.4.22 to 9.6.1 i noticed, 
under heavy beanchmarks, a really slowdown of Postgresql 9.6.1, with 
the machine really "without breath".

By replacing Postgresql 8.4.22 evberything returns working fine.
With three days of investigation, i come at a function with lots of 
joing and conditions.

Only by removing this condition:
"exists ( select 1 from gruorari where 
gruorari.idgrucate=grucategorie.id and ( (('{'||gg_sett||'}')::int[] 
&& array[EXTRACT(DOW FROM NOW())::int])='t' and  now()::time between 
gruorari.dalle::time and gruorari.alle::time) )"

The benchmark with Postgresql 9.6.1 version are now very fast.
The table metnioned by the query has got indexes:
 id | numeric(1000,1)   | not null default function_get_next_sequence('gr
uorari_id_seq'::text)
 idgrucate | numeric(1000,1)   |
 dalle | character varying |
 alle  | character varying |
 gg_sett   | character varying |
 azione| character varying |
Indexes:
"keygruorari" PRIMARY KEY, btree (id)
"alle_idx" btree (alle)
"dalle_idx" btree (dalle)
"gg_sett_idx" btree (gg_sett)
"idgrucate_idx" btree (idgrucate)
What is strange, is that with 8.4.22 version there is no problem, but 
there is something that does not deal with 9.6.1 version.

Could you please help me?

THANK YOU!
/F

You might want to include the query plans for each server

Re: [GENERAL] COPY: row is too big

[Rob Sargent]

On 01/05/2017 11:46 AM, Adrian Klaver wrote:

On 01/05/2017 08:31 AM, Rob Sargent wrote:



On 01/05/2017 05:44 AM, vod vos wrote:

I finally figured it out as follows:

1. modified the corresponding data type of the columns to the csv file

2. if null values existed, defined the data type to varchar. The null
values cause problem too.

so 1100 culumns work well now.

This problem wasted me three days. I have lots of csv data to COPY.



Yes, you cost yourself a lot of time by not showing the original table
definition into which you were trying insert data.


Given that the table had 1100 columns I am not sure I wanted to see it:)

Still the OP did give it to us in description:

https://www.postgresql.org/message-id/15969913dd3.ea2ff58529997.7460368287916683127%40zoho.com 

"I create a table with 1100 columns with data type of varchar, and 
hope the COPY command will auto transfer the csv data that contains 
some character and date, most of which are numeric."


In retrospect I should have pressed for was a more complete 
description of the data. I underestimated this description:


"And some the values in the csv file contain nulls, do this null 
values matter? "



My apologies for missing that.  Was sure there would be room for some 
normalization but so be it: OP's happy, I'm happy





--
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] Materialized view vs. view

[Rob Sargent]

> 
> 
> "A rose by any other name would still smell as sweet”.
Actually there’s no “still” in that line, if you’re quoting Shakespeare.  And 
the full “That which we call a rose …” is truly appropriate here.



-- 
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] update error with serializable

[Rob Sargent]

On 01/20/2017 10:05 AM, Kevin Grittner wrote:

On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo  wrote:


I've two threads countinuously updataing rows in the same table.
Each one does: BEGIN, UPDATE,UPDATECOMMIT
There can't be two active transactions updating the same row (my
bug apart but I don't think so).
I'm using default_transaction_isolation = 'serializable'
I get "could not serialize access due to read/write dependencies
among transactions"
I din't expect to see it, hence there must be something  in
postgresql theory that I haven't understood well and I'd like a
clarification.

Most likely one or both transactions have have updated 3 or more
tuples on a single page, causing the tuple locks for the
transaction on that page to be combined into a single page lock for
that transaction.  This is intended to prevent the memory required
for tracking predicate locks from growing too large.  This
threshold of 3 per page was entirely arbitrary and always seen as
something which could and should be improved someday.  That might
happen for version 10 (expected to be released next year), since a
patch has been submitted to make that configurable.

https://www.postgresql.org/message-id/flat/d8joa0eh9yw@dalvik.ping.uio.no#d8joa0eh9yw@dalvik.ping.uio.no

If you are able to build from source, you might want to test the
efficacy of the patch for your situation.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Configurable or dynamic?  Wouldn't something related to tuples per page 
(and maybe fillfactor) do the trick?




--
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 is table not found?

[Rob Sargent]

You must quite as "Raum"

> On Jan 31, 2017, at 6:45 AM, Egon Frerich  wrote:
> 
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> I created a db 'Hausrat' with a table "Raum". If I look in pgadmin I
> find the the table. In sql field is shown:
> 
>> -- Table: "Raum"
>> 
>> -- DROP TABLE "Raum";
>> 
>> CREATE TABLE "Raum"
>> (
>>  "RaumID" serial NOT NULL, -- Automatisch vergebenes
> Identifizierungsmerkmal für den Raum
>>  "Raum" character varying(15), -- Bezeichnung des Raums
>>  CONSTRAINT "Raum_pkey" PRIMARY KEY ("RaumID")
>> )
>> WITH (
>>  OIDS=FALSE
>> );
>> ALTER TABLE "Raum"
>>  OWNER TO egon;
>> COMMENT ON COLUMN "Raum"."RaumID" IS 'Automatisch vergebenes
> Identifizierungsmerkmal für den Raum';
>> COMMENT ON COLUMN "Raum"."Raum" IS 'Bezeichnung des Raums';
>> 
>> 
>> -- Index: "Raum_RaumID_idx"
>> 
>> -- DROP INDEX "Raum_RaumID_idx";
>> 
>> CREATE INDEX "Raum_RaumID_idx"
>>  ON "Raum"
>>  USING btree
>>  ("RaumID");
>> 
> But psql tells me "Raum" is not existent:
> 
>> egon@xfEinzel ~ $ psql Hausrat
>> psql (9.3.15)
>> Type "help" for help.
>> 
>> Hausrat=# SELECT * FROM Raum;
>> ERROR:  relation "raum" does not exist
>> LINE 1: SELECT * FROM Raum;
>>  ^
>> Hausrat=#
> 
> Why?
> 
> Egon
> 
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v2.0.22 (GNU/Linux)
> 
> iQIbBAEBAgAGBQJYkJTwAAoJECc7nbY+bg5uiRQP9iC4UtbHpPvdvV796fxcMSFp
> dncPHzkTKHvcEh3V9t/Xn/BzEHBDAO6RO8jmU18Ja9f/0nOyNXrWxal0OJZXint5
> l3/qRKbekkl7zfogysh4XgZQkpWwsJcYDGoG5tCUQm8TJ3sCk9a9+mbkDhy0Vsev
> MPeyYl/fYvlTBkiRmlPZTOX7YjRzeryKXguB3PBke3Vo6SZ1sFWnMjJ7bc2b
> 4cW9hmInmwXZ4EBOfcUW4QnfM0mgHBMGxJwYVQfeC6fQeqT8emh0KQLqozjFe/tk
> KNKDU0RebcrBgXP/lIGI34XahZW+WOdLli/t8wBUNiINruY2FlizuH6Uuak3tLjC
> cD7vi0SzNK2YFr9Rozw8ow9WVxSfjWBLiOA1GkFdhxKb80NLHthzo8oIVaCqz0bj
> 71kA0ewgZ9NMay6ch1VjqSPPFhAZHc1Ho3xIAa0BsZpvEdccDBsL+yk/6DGDYua9
> 5oT1p6CZqfDJqxEIoUiNaJEKm3An5ySN8hHn527/apG9yA9QMh5qJPHA0wRqtLxN
> hNSxugKhS6UOw/Wivbx0OIhN8jqBv4m22UgO9pFGgxHLs1hheSTMUSbExFCLuK+z
> sS1Kw9syplk+lFTzK6mqNpr3BQ6v2fmkPmRTZoID4e9T3DY8Bna2JXG2U1QGEzwa
> kwpJOMAvY3DDPv3pIK8=
> =i8FY
> -END PGP SIGNATURE-
> 
> 
> 
> 
> -- 
> 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

Re: [GENERAL] Generating an XSD file from an existing database

[Rob Sargent]

> On Dec 6, 2015, at 2:30 PM, Blake McBride  wrote:
> 
> Greetings,
> 
> I need an XML specification for my exiting schema - an XSD file.  Reading the 
> docs I see schema_to_xml but I do not understand:
> 
> A.  Will that produce the XSD file I seek?
> 
> B.  I do not understand the documentation.  Can I do this through psql?  I 
> need an exact example if possible.
> 
> Thank you.
> 
> Blake McBride
> 
Yes, those functions are available through psql
select schema_to_xml()
and
select schema_to_xmlschema() are what you’re after.

Redirect the output  of each into separate files.




-- 
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] Convert 2 foreign key values within the same SELECT

[Rob Sargent]

On 01/04/2016 12:36 PM, gvim wrote:
I have a query which successfully retrieves id values "me" and "you" 
when 2 planetary values are supplied:


SELECT l.me_id AS me, l.you_id AS you,
a.l AS left, a.aspect, a.r AS right, l.id AS link_id, 
c.comment

FROM aspects a, links_aspects la, links l, syn_comments c
WHERE (a.r = 'Venus' AND a.l = 'Venus')
  AND a.type = 'synastry'
  AND la.aspect_id = a.id
  AND la.id = c.links_aspects_id
  AND la.link_id = l.id

 me  | you | left  | aspect | right | link_id |  comment
-+-+---++---+-+--- 


 365 | 371 | Venus | cnj| Venus |  49 | Garry/Dom Venus/Venus syn
(1 row)


. however, l.me_id and l.you_id are foreign keys referencing a 
users table from which I would like to retrieve concat(u.first_name, ' 
', u.surname) AS name for l.me_id and l.you_id. It seems simple but I 
can't think of a JOIN which will do the trick.


gvim



Join to user table twice, once for you once for 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] Support for BDR in 9.5?

[Rob Sargent]

On 01/08/2016 10:39 AM, Andrew Biggs (adb) wrote:
Can anyone tell me if PostgreSQL 9.5 supports (either natively or by 
extension) the BDR functionality?


I tried it out and ran into issues, but it could well have been I was 
doing something wrong.


Thanks!
Andrew


I'm sure those who might be able to help you would be interested in 
what, exactly, it was you tried.

Re: [GENERAL] Let's Do the CoC Right

[Rob Sargent]

On 01/22/2016 03:53 PM, Andrew Sullivan wrote:
This is why I posted all that stuff about what the IETF does some 
while ago. There is definitely more than one way to do this. Best 
regards, A 
Just a gut feeling, but I think this thread had driven the rest of the 
regulars to drink at a bar without wifi




--
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] Partitioning and ORM tools

[Rob Sargent]

On 03/22/2016 12:55 PM, Melvin Davidson wrote:
Your problem seems strange as it has never been previously reported 
for anyone else that has _successfully_ set up partioning.
Perhaps is you provide just a little bit more detail we might be able 
to help you.

Useful and needed information would be:
1. Version of PostgreSQL
2. Operating System
3. Table structure for partitioned table
4. Trigger function and trigger used for insert
5. The actual insert statement.

On Tue, Mar 22, 2016 at 2:40 PM, CS DBA > wrote:


Hi All;

we setup partitioning for a large table but had to back off
because the return status (i.e: "INSERT 0 1") returns "INSERT 0 0"
when inserting into the partitioned table which causes the ORM
tool to assume the insert inserted 0 rows. Is there a standard /
best practices work around for this?

Thanks in advance




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




And which ORM are you using.

I take it the problem is that the ORM is saying "zero rows inserted" and 
that's um, er, upsetting the client ( which might decide to retry and 
then generates an error for non-unique key or some such noise)


rjs



--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Partitioning and ORM tools

[Rob Sargent]

On 03/22/2016 03:00 PM, Joshua D. Drake wrote:

On 03/22/2016 01:50 PM, CS DBA wrote:


Understood, was just wondering if there is a way to cause the child
table insert results to be returned to the ORM/Application instead of
the master/base table insert


Insert into the child table directly based on the partition rules.

JD


I would think the ORM (as yet undefined) would want to think in terms of 
the parent table and not know about the physical schema details.
Can  the client not be written to check only for errors vs checking for 
non-zero inserts?




--
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] Table size for partitioned setup

[Rob Sargent]

On 03/28/2016 02:41 PM, Mat Arye wrote:

Hi All,

I am writing a program that needs time-series-based insert mostly 
workload. I need to make the system scaleable with many thousand of 
inserts/s. One of the techniques I plan to use is time-based table 
partitioning and I am trying to figure out how large to make my time 
tables.


Does anybody have any hints on optimal table sizes either in terms of 
rows or in terms of size? Any rule of thumbs I can use for table size 
in relation to amount of memory on the machine? Is the size of the 
index more important than the size of the table (if queries mostly use 
indexes)?


Basically, I am asking for pointers about how to think about this 
problem and any experiences people have had.


Thanks,
Mat

P.S. I am aware of limits listed here: 
http://www.postgresql.org/about/. I am asking about practical size 
limits for performance consideration.

Your current hardware, or hardware budget might play into the answer.



--
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] Table size for partitioned setup

[Rob Sargent]

On 03/28/2016 02:55 PM, Mat Arye wrote:

This will run on EC2 (or other cloud service) machines and on ssds.
Right now runs on m4.4xlarge with 64GiB of ram.
Willing to pay for beefy instances if it means better performance.


On Mon, Mar 28, 2016 at 4:49 PM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote:




On 03/28/2016 02:41 PM, Mat Arye wrote:

Hi All,

I am writing a program that needs time-series-based insert
mostly workload. I need to make the system scaleable with many
thousand of inserts/s. One of the techniques I plan to use is
time-based table partitioning and I am trying to figure out
how large to make my time tables.

Does anybody have any hints on optimal table sizes either in
terms of rows or in terms of size? Any rule of thumbs I can
use for table size in relation to amount of memory on the
machine? Is the size of the index more important than the size
of the table (if queries mostly use indexes)?

Basically, I am asking for pointers about how to think about
this problem and any experiences people have had.

Thanks,
Mat

P.S. I am aware of limits listed here:
http://www.postgresql.org/about/. I am asking about practical
size limits for performance consideration.

Your current hardware, or hardware budget might play into the answer.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Those who supply real answers on this list um, er, discourage 
top-posting. (Not my fave, but there you go)

Re: [GENERAL] Non-default postgresql.conf values to log

[Rob Sargent]

> On Apr 7, 2016, at 8:26 AM, Alex Ignatov  wrote:
> 
> 
> 
>> On 07.04.2016 16:53, Tom Lane wrote:
>> Alex Ignatov  writes:
>>> My question is: is there any option(s) to log non-default
>>> postgresql.conf values to log file?
>> No, but you can easily find all the non-default settings by querying
>> the pg_settings view.
>> 
>>regards, tom lane
> 
> Yeah, i know it but pg_settings is available only after pg is ready to accept 
> client connections.
> Also in some cases log file is placed on another server say, syslog. And if 
> your pg server is not available you cant say anything about your pg_settings.

At that point you're looking in a file: log file or config file. Does it matter 
which?
> -- 
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
> 
> 
> 
> -- 
> 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

Re: [GENERAL] 20160417105248.d20dcefed39b5d9031c6b...@potentialtech.com

[Rob Sargent]

> On Apr 17, 2016, at 12:41 PM, Sergei Agalakov  
> wrote:
> 
> I know about DBSteward. I don't like to bring PHP infrastructure only to be 
> able to compare two dumps,
> and to deal with potential bugs in the third party tools. The pg_dump in 
> other hand is always here, and is always trusted.
> SQLWorkbench/J also can compare two schemas, and requires only Java. Again, I 
> trust pg_dump more.
> http://www.sql-workbench.net/
> 
> May be pg_dump was never INTENDED to generate the dump files with the 
> determined order of the statements,
> but it CAN do it with the minor changes, and be more useful to 
> administrators. Why rely on the third party tools
> for the tasks that can be done with the native, trusted tools?
> 
> Sergei
Does it matter if they differ if you cannot recreate the correct one exactly 
from source-controllled DDL?  Or know how they are supposed to differ if this 
is a migration point?



-- 
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] migration from PointBase to PostgreSQL

[Rob Sargent]

1. get an ascii dump of the source database.

2. check if for consistency if you're ambitious or suspicion by nature
(or you've been through this a number of times before and discovered
that this is _always_ necessary)

3. create the target database/schema

4. convert source data to target as per differences in the two schema

5. resolve all the inconsistencies you missed in the first pass

6. load the tables of the target via COPY, starting with those that have
no foreign keep dependencies, followed by their dependents.

7. resolve all the inconsistencies you missed in the second pass

repeat 6 and 7 'til the cows come home



On 10/07/2010 10:35 AM, Frank Zhao wrote:
> We are supposed to migrate PointBase to PostgreSQL.
> 
> Do you have any advice on how we should approach this, and how to do
> estimation on the workload?
> 
> The current PointBase is running on Hub and Spoke by using UniSync for
> backup and restore purposes. Is there any similar technology of UniSync
> in PostgreSQL.
> 
> 
> I am new to both PostgreSQL and PointBase.
> 
>  
> 
>  
> 
> Thank you in advance.
> 
>  
> 
> Regards,
> 
> Frank
> 
>  CAUTION - Disclaimer *
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely 
> for the use of the addressee(s). If you are not the intended recipient, 
> please 
> notify the sender by e-mail and delete the original message. Further, you are 
> not 
> to copy, disclose, or distribute this e-mail or its contents to any other 
> person and 
> any such actions are unlawful. This e-mail may contain viruses. Infosys has 
> taken 
> every reasonable precaution to minimize this risk, but is not liable for any 
> damage 
> you may sustain as a result of any virus in this e-mail. You should carry out 
> your 
> own virus checks before opening the e-mail or attachment. Infosys reserves 
> the 
> right to monitor and review the content of all messages sent to or from this 
> e-mail 
> address. Messages sent to or from this e-mail address may be stored on the 
> Infosys e-mail system.
> ***INFOSYS End of Disclaimer INFOSYS***
> 

-- 
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] Understanding PostgreSQL Storage Engines

[Rob Sargent]

On 10/08/2010 03:39 PM, Adrian Klaver wrote:
> On Friday 08 October 2010 2:30:40 pm Carlos Mennens wrote:
>> I know that MySQL uses MyISAM storage engine by default and was just
>> trying to look on Google to try and see if I could understand what
>> storage engine does PostgreSQL use by default when I generate a
>> database / table. Is there some way someone (me) who knows nothing
>> about how a ORDBMS works understand the difference between all storage
>> engine options and which does PostgreSQL use by default.
>>
>> Thanks for any help...
>>
>> -Carlos
> 
> Postgres only has one storage engine. Sort of simplifies things.
> 
My guess is the OP wants to know that Postgres uses tactically in its
engine: B(+)trees (or whatever it does actually use) versus Indexed
Sequential Access Method (judging by the name). No?

-- 
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] Postgres won't start after setting ssl=on

[Rob Sargent]

Does postgres run as root or as the postgres user. I suspect you want 
postgres to own that file.


Mike Christensen wrote:

Hi, I'm trying to require SSL for Postgres connections from certain
IPs..  This is on Postgres 9.0.

First, I've followed the directions at:

http://www.postgresql.org/docs/9.0/static/ssl-tcp.html

I've created the files server.crt and server.key.  I've also removed
the passphrase from the key so Postgres can start automatically.
Finally, I ran:

chmod 0600 server.key

The permissions on server.key are now:

-rw--- 1 root root 887 Oct 10 03:42 server.key

However, when I set ssl = on in postgresql.conf and start the server,
I get the logged error:

2010-10-10 03:47:07 UTC FATAL:  could not load private key file
"server.key": Permission denied

I'm logged on as root.  Any ideas?  Thanks!

Mike

  


--
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] Adding a New Column Specifically In a Table

[Rob Sargent]

On 10/14/2010 04:32 AM, Thom Brown wrote:
> On 13 October 2010 23:19, Raymond O'Donnell  wrote:
>> On 13/10/2010 19:04, Carlos Mennens wrote:
>>>
>>> OK so I have read the docs and Google to try and find a way to add a
>>> new column to an existing table. My problem is I need this new column
>>> to be created 3rd  rather than just dumping this new column to the end
>>> of my table. I can't find anywhere how I can insert my new column as
>>> the 3rd table column rather than the last (seventh). Does anyone know
>>> how I can accomplish this or if it's even possible. Seems like a
>>> common task but I checked the documentation and may have missed it in
>>> my reading.
>>>
>>
>> It isn't possible at the moment. This has come up a good bit in the past, so
>> you'll find debate in the archives...
>>
>> One work-around is to add the column, and then do:
>>
>>  create table new_table as
>>select [columns in desired order] from old_table;
>>
>>  drop table old_table;
>>
>>  alter table new_table rename to old_table;
>>
>> ...remembering to deal with foreign key constraints as you go.
> 
> ..and indexes, triggers, rules, views and every other dependency.
> It's a potentially tricky solution to something which shouldn't really
> be a problem.
> 
True indeed. I suspect OP is stuck using some (crud-ie?) "platform"
which is automagically producing the presentation so this approach
(drop,recreate all involved) is likely to be the best bet.  My guess is
the number of users isn't huge (still setting schema, not adding
employer_id, etc) so this approach would be a pretty quick and testable fix.


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

Re: [GENERAL] how to get current sql execution time?

[Rob Sargent]

\timing

On 10/14/2010 04:47 PM, sunpeng wrote:
> when I use the psql to send a sql, how to get current sql execution time?

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

Re: [GENERAL] how to write an optimized sql with two same subsql?

[Rob Sargent]

On 10/14/2010 05:34 PM, sunpeng wrote:
> We have a table A:
> CREATE TABLE A(
>uid integer,
>groupid integer
> )
> Now we use this subsql to get each group's count:
> SELECT count(*) as count
> FROM A
> GROUP BY groupid
> ORDER BY groupid
> 
> Then we try to find the group pair with following conditions:
> SELECT c.groupid as groupid1,d.groupid as groupid2
> FROM subsql as c, subsql as d
> WHERE d.groupid > c.groupid
>   and d.count > c.count;
> 
> Does that mean subsql will be executed twice? or how to write the
> optimized sql?
> 
> 
What are you trying to discover about groups and their frequency in
tablea?  Does the numberical value of groupid have any meaning in your
system?


-- 
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] installing from source in Windows

[Rob Sargent]

No at all familiar with MinGW, but I've done some "./configure"s in my time.

./configure scripts 1) check to see what the local build environment 
contains and 2) which special feature you wish to toggle.


But the pretty much assume a unix-like env. Could you put cygwin on 
rather than just mingw?


Turner, John J wrote:


I have MinGW installed and I’ve unpacked the pg 9.0 source files which 
now reside in the following path:


· C:\Program Files\postgresql-9.0.0

In Chapter 15.5 of the 9.0 manual, the installation procedure 
basically begins by instructing me to run_ ./configure_


This command does not work.

Much appreciated if someone could point me in the right direction or 
clue me in to what I need in order to run the configuration script.


Thanks,

John



--
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] Constraint: string length must be 32 chars

[Rob Sargent]

Merlin Moncure wrote:

On Sat, Oct 16, 2010 at 12:15 PM, Alexander Farber
 wrote:
  

Hello,

I'm trying to create a table, where md5 strings will serve as primary keys.
So I'd like to add a constraing that the key length should be 32 chars long
(and contain [a-fA-F0-9] only):



why don't you use the bytea type, and cut the key size down 50%?  You
can always format it going out the door if you want it displayed hex.
Besides being faster, you get to skip the 'is hex' regex.

create table foo(id bytea check(length(id) = 16));
insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
insert into foo values (digest('b', 'md5')); -- if using pgcrypto (preferred)

select encode(id, 'hex') from foo;

merlin

  


Why not the support uuid type instead.  Aren't md5s only as unique as 
the source?  i.e. The same value hashed results in the same md5, no?


--
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] Constraint: string length must be 32 chars

[Rob Sargent]

I just read the "anonymously" part, so I take it you have ruled out 
recording the given coordinate components directly, in multiple columns 
presumably?  Otherwise it seems you could then do a) a composite key and 
b) queries directly against coordinate values.




Alexander Farber wrote:

Thank you for your advices.

I actually would like to store GPS coordinates, but anonymously,
so I was going to save md5(my_secret+IMEI) coming from a mobile...

I have to lookup if uuid is supported there

Regards
Alex

On Sat, Oct 16, 2010 at 11:08 PM, Rob Sargent  wrote:
  

Merlin Moncure wrote:


why don't you use the bytea type, and cut the key size down 50%?  You
can always format it going out the door if you want it displayed hex.
Besides being faster, you get to skip the 'is hex' regex.

create table foo(id bytea check(length(id) = 16));
insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
insert into foo values (digest('b', 'md5')); -- if using pgcrypto
(preferred)

select encode(id, 'hex') from foo;
  

Why not the support uuid type instead.  Aren't md5s only as unique as the
source?  i.e. The same value hashed results in the same md5, no?



  


--
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] Constraint: string length must be 32 chars

[Rob Sargent]

Alexander Farber wrote:

Hello, really good advices here! But -

On Sun, Oct 17, 2010 at 2:37 AM, Rob Sargent  wrote:
  

I just read the "anonymously" part, so I take it you have ruled out
recording the given coordinate components directly, in multiple columns
presumably?  Otherwise it seems you could then do a) a composite key and b)
queries directly against coordinate values.



what do you mean here? Do you suggest using line segments
instead of points in my records or something else?

Regards
Alex

  



Sorry Alexander, I have to confess here and now that I'm not familiar 
with GPS data.  My presumption was that it was represented as something 
like latitude, longitude (,alitutde?, others?), and that while those 
would surely hash nicely with md5 I thought you would be loosing the 
inherent information of the position.


--
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] Composite Index question

[Rob Sargent]

Hm. Run some queries; drop the second version of the index definition;
re-run the same queries; report to the group.  The redundant index isn't
helping, that much is certain.

On 10/20/2010 05:43 PM, DM wrote:
> Composite Index question:
> 
> I have composite index on 3 columns on a table, by mistake the composite
> index was created twice on the table.
> 
> Will there any performance issues on this table because of the 2 same
> composite indexes?
> 
> Thanks
> Deepak

-- 
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] Composite Index question

[Rob Sargent]

If you can think of one benefit from having the redundant index then by
all means keep it.  It certainly eludes me.  Seems to me, removing an
un-necessary index on a huge table can only be a good thing.

On 10/20/2010 06:02 PM, DM wrote:
> Its a huge table in production, i dont want to take any risk.
> 
> I can simulate and test this but i was to checking to see If any one
> knows off hand about this.
> 
> 
> 
> I can simulate it but
> On Wed, Oct 20, 2010 at 4:57 PM, Rob Sargent  <mailto:robjsarg...@gmail.com>> wrote:
> 
> Hm. Run some queries; drop the second version of the index definition;
> re-run the same queries; report to the group.  The redundant index isn't
> helping, that much is certain.
> 
> On 10/20/2010 05:43 PM, DM wrote:
> > Composite Index question:
> >
> > I have composite index on 3 columns on a table, by mistake the
> composite
> > index was created twice on the table.
> >
> > Will there any performance issues on this table because of the 2 same
> > composite indexes?
> >
> > Thanks
> > Deepak
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org
> <mailto: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

Re: [GENERAL] Custom cache implemented in a postgresql C function

[Rob Sargent]

Are you sure you cache needs to grow endlessly?  Otherwise you could use
RequestAddinShmemSpace and manage you're map within that space, perhaps
"overwriting" chunks on an LRU basis or a rollover. i.e. Grab it all and
do your own management within that single block of shmem.
Caches are best for thing revisited often, so old/unused ought to be
expendable with little performance loss, at least compared with the
heavy traffic.

On 10/20/2010 05:44 PM, Gabi Julien wrote:
> Hi,
> 
> Here is my problem: I have a postgresql C function that looks like this:
> 
> Datum filter(PG_FUNCTION_ARGS);
> 
> It takes identifiers and queries a bunch of tables and ends up returning true 
> or false. So far nothing difficult except that we want better performance. 
> The function was already optimized to the best of my abilities and changing 
> the structure of the database would not help. However, having a cache would 
> be the perfect solution. I could implement this cache outside of postgresql 
> if need be but nothing could beat implementing this directly in a postgresql 
> C function.
> 
> So this is what I want, a custom cache built into a postgresql C function. 
> Since postgresql uses different processes, it would be best to use the shared 
> memory. Can this be done safely? At its core, the cache could be considered 
> as simple as a map protected by a mutex. With postgresql, I first need to 
> initialized some shared memory. This is explained at the end of this link:
> 
> http://www.postgresql.org/docs/8.2/static/xfunc-c.html
> 
> However, it sounds like I need to reserve the shared memory in advance using:
> 
> void RequestAddinShmemSpace(int size)
> 
> In my case, I do not know how big my cache will be. I would preferably 
> allocate the memory dynamically. Is this possible? In any case, am I trying 
> to reinvent the wheel here? Is there already a shared map or a shared hash 
> structure available in postgresql?
> 
> If shared memory turns out too difficult to use, I could create separate 
> caches for each postgresql processes. This would be a waste of space but it 
> might be better then nothing. In this case, do I need to make my code thread 
> safe? In other words, is postgresql using more then one thread per processes?
> 
> Any insights would be more then welcome!
> Thank you,
> Gabi Julien
> 

-- 
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] Composite Index question

[Rob Sargent]

Sorry, I cannot prove or disprove any penalty.  I don't currently have
access to any postgres db of any serious magnitude.

On 10/20/2010 06:23 PM, DM wrote:
> I know there is no benfit of having duplicate indexes.
> Inorder for me to make change on production it requires lot of approvals
> and stuff.
> 
> I wnat to know if there is any major performance drawback for having
> duplicate composite index, so that i can push hard for the change. Let
> me know.
> 
> thanks for your looking into this.
> 
> 
> On Wed, Oct 20, 2010 at 5:10 PM, Rob Sargent  <mailto:robjsarg...@gmail.com>> wrote:
> 
> If you can think of one benefit from having the redundant index then by
> all means keep it.  It certainly eludes me.  Seems to me, removing an
> un-necessary index on a huge table can only be a good thing.
> 
> On 10/20/2010 06:02 PM, DM wrote:
> > Its a huge table in production, i dont want to take any risk.
> >
> > I can simulate and test this but i was to checking to see If any one
> > knows off hand about this.
>     >
> >
> >
> > I can simulate it but
> > On Wed, Oct 20, 2010 at 4:57 PM, Rob Sargent
> mailto:robjsarg...@gmail.com>
> > <mailto:robjsarg...@gmail.com <mailto:robjsarg...@gmail.com>>> wrote:
> >
> > Hm. Run some queries; drop the second version of the index
> definition;
> > re-run the same queries; report to the group.  The redundant
> index isn't
> > helping, that much is certain.
> >
> > On 10/20/2010 05:43 PM, DM wrote:
> > > Composite Index question:
> > >
> > > I have composite index on 3 columns on a table, by mistake the
> > composite
> > > index was created twice on the table.
> > >
> > > Will there any performance issues on this table because of
> the 2 same
> > > composite indexes?
> > >
> > > Thanks
> > > Deepak
> >
> > --
> > Sent via pgsql-general mailing list
> (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>
> > <mailto:pgsql-general@postgresql.org
> <mailto: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

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Rob Sargent]

On 10/21/2010 10:27 AM, Scott Marlowe wrote:
> On Thu, Oct 21, 2010 at 9:33 AM, Brian Hirt  wrote:
>>>
>>> There are only two tables in the query.
>>>
>>
>> Tim,
>>
>> No, your query is written incorrectly.  I don't understand why you come on 
>> to this list all hostile and confrontational.  Regardless, people still try 
>> to help you and then you still ignore the advice of people that are giving 
>> you the solutions to your problems.
> 
> Maybe he's used to paid commercial support where people are often
> quite rude and hostile to the support staff to try and "motivate" them
> or something?  I've seen it before for sure.
> 
> Again, OP, what does EXPLAIN say about this query?
> 

Maybe I should re-read, but I didn't feel any confrontation.
Frustration for sure.  OP has clearly tried pretty hard, on some tricky
bits too, but I'm betting all for naught if (as seems likely) it's just
mistaken sql.  "update from" is NOT straight forward.

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