Re: [GENERAL] Selectively Importing Data

[Andy Colson]

On 10/30/2015 07:24 PM, David Blomstrom wrote:

First consider the following table:

create table taxon (
   taxonid serial,
   descr text
);

As I understand it, "serial" means that column will automatically populate with 
a numerical key.

If I want to fill the field 'descr' with a list of scientific names stored in a 
spreadsheet, then how would I proceed?

I have a spreadsheet that has about a dozen columns. I copied the field with 
scientific names into a new spreadsheet. Then I created an empty field on the 
left. So I have a spreadsheet with two columns. I saved it as a CSV file.

When I try to import it with pgAdmin III, I get the error message

WARNING: null value in column "taxonid" violates non-null constraint

How can I import that single column into the second column in this table? 
Thanks.


Try using just one column?

Or, create taxonid as integer, then fill the spreadsheet column with int's, and 
import both columns.

-Andy


--
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] Upgrade from 9.3 to 9.4 issue

[Tom Lane]

Stephen Davies  writes:
> I have just upgraded from Fedora 21 to 22.
> This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes 
> postmaster 
> to fail because the existing databases are still at 9.3.
> As suggested, I then ran postgresql-setup --upgrade but this failed with:
> lc_collate cluster values do not match:  old "en_US.UTF-8", new "en_AU.UTF-8"

Hm, apparently you changed the system-wide language setting between F21
and F22?

One thing you should do is file a bug in Red Hat's bugzilla, pointing out
that postgresql-setup needs to endeavor to create the new cluster with
lc_collate and lc_ctype matching the old one.

Then, depending on what you want to do:

1. You really want to switch to en_AU, not just system-wide but for the
database: gonna have to dump and reload, I'm afraid.  pg_upgrade will
not handle this scenario.

2. You want to switch to en_AU system-wide but it's okay to leave the
database running in en_US: what I'd do personally is edit the
postgresql-setup shell script and add "export LANG=en_US.UTF-8" just
before the initdb call.  This won't stick across your next Fedora
upgrade, but hopefully by then Red Hat will have addressed your bug.

3. This was a mistake and you'd rather stay in en_US all round:
you can probably change the system-wide language setting somewhere,
but I do not remember where right at the moment.

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

[GENERAL] Upgrade from 9.3 to 9.4 issue

[Stephen Davies]

I have just upgraded from Fedora 21 to 22.
This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes postmaster 
to fail because the existing databases are still at 9.3.

As suggested, I then ran postgresql-setup --upgrade but this failed with:

Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is a superuser   ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking for invalid "line" user columnsok
Creating dump of global objects ok
Creating dump of database schemas
  books
  postgres
  pots
  stocks
  template1
ok

lc_collate cluster values do not match:  old "en_US.UTF-8", new "en_AU.UTF-8"
Failure, exiting

How can I recover from here?

Cheers and thanks,
Stephen Davies


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

[GENERAL] Selectively Importing Data

[David Blomstrom]

First consider the following table:

create table taxon (
  taxonid serial,
  descr text
);

As I understand it, "serial" means that column will automatically populate
with a numerical key.

If I want to fill the field 'descr' with a list of scientific names stored
in a spreadsheet, then how would I proceed?

I have a spreadsheet that has about a dozen columns. I copied the field
with scientific names into a new spreadsheet. Then I created an empty field
on the left. So I have a spreadsheet with two columns. I saved it as a CSV
file.

When I try to import it with pgAdmin III, I get the error message

WARNING: null value in column "taxonid" violates non-null constraint

How can I import that single column into the second column in this table?
Thanks.

Re: [GENERAL] Hierarchical Query Question (PHP)

[David Blomstrom]

 I think I answered my question @
http://www.the-art-of-web.com/sql/lookup-table/

It sounds like the field taxon_id is similar to the field id - it's just
automatically populated by a numerical key. So if I add a new taxon on row
5, then the taxon_id for the following row with change from 5 to 6 - right?

It therefore sounds like the field parent_id is useless for this particular
exercise and could be deleted - unless I find some other use for it.

Re: [GENERAL] Hierarchical Query Question (PHP)

[David Blomstrom]

Sorry, I don't think I specified that at the beginning.

My original/master table has several fields, four of which are relevant to
this hierarchical stuff - id, taxon, parent, parent_id.

The first is a numerical key, from 1 to probably somewhere around 8,000 for
mammals, 1 to 10,000 for birds, etc. The field taxon holds the names of
taxons (e.g. 'Mammalia', 'Canis-lupus'). The field parent holds the names
of each taxon's parent. The field parent_id is just a series of numerals
matching each taxonomic level. The value is 1 for Mammalia, 2 for each
mammal order, 3 for each family, 4 for genera and 5 for species. It looks
something like this:

ID | TAXON | PARENT | PARENT_ID
1 | Mammalia | Chordata | 1
2 | Carnivora | Mammalia | 2
3 | Canidae | Carnivora | 3
4 | Canis | Canidae | 4
5 | Canis-lupus | Canis | 5
6 | Canis-latrans | Canis | 5

I wonder if that's what you had in mind with taxonid. Should I just rename
parent_id to taxon_id?

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

[David Blomstrom]

 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?

Re: [GENERAL] Hierarchical Query Question (PHP)

[David Blomstrom]

Yes, I guess it does make sense to keep a copy of your actions.

In the meantime, I now have two new tables with the following schema:

-- Table: public.taxon

-- DROP TABLE public.taxon;

CREATE TABLE public.taxon
(
  taxonid integer NOT NULL DEFAULT nextval('taxon_taxonid_seq'::regclass),
  descr text
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.taxon
  OWNER TO postgres;


* * * * *

-- Table: public.gz_life_mammals

-- DROP TABLE public.gz_life_mammals;

CREATE TABLE public.gz_life_mammals
(
  id integer NOT NULL DEFAULT nextval('gz_life_mammalsx_id_seq'::regclass),
  taxonid integer,
  parentid integer
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.gz_life_mammals
  OWNER TO postgres;

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)

[David Blomstrom]

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

Re: [GENERAL] Hierarchical Query Question (PHP)

[Adrian Klaver]

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


--
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] Hierarchical Query Question (PHP)

[Raymond O'Donnell]

On 30/10/2015 22:29, 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.

There's an "Execute query" button in the toolbar, and pressing F5 does
the same. If the toolbar isn't visible, you can make it visible under
the "View" menu.

As a bonus, you can select a block of SQL and only the selected code
will get executed.

Ray.

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


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

Re: [GENERAL] Hierarchical Query Question (PHP)

[David Blomstrom]

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.

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

Re: [GENERAL] Hierarchical Query Question (PHP)

[Raymond O'Donnell]

On 30/10/2015 22:10, 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"?

Yes, it will. I haven't seen, what went before in this thread so may
have missed stuff, but you should also add a foreign key constraint
between the tables (for taxonid anyway, dunno what parentid refers to):

create table gz_life_mammals (
   id serial,
   taxonid integer, -- use the lookup table
   parentid integer, -- use the lookup table

  constraint mammals_taxon_fk foreign key (taxonid) references
taxon(taxonid)

);


If parentid also references taxon.taxonid, add a similar constraint for
it too,

Ray.


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


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

Re: [GENERAL] 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)

[David Blomstrom]

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

Re: [GENERAL] Hierarchical Query Question (PHP)

[Andy Colson]

On 10/30/2015 4:36 PM, Andy Colson wrote:

On 10/30/2015 3:47 PM, David Blomstrom wrote:

No, I get the same T_FUNCTION error.

Someone commented that the function...

   create function tax_rank(id integer) returns text as $$
 select case id
  when 1 then 'Classes'
  when 2 then 'Orders'
  when 3 then 'Families'
  when 4 then 'Genera'
  when 5 then 'Species'
end;
   $$ language sql;

...should ideally be part of the table schema. Does that mean I need to
go into pgAdmin, open up my table and paste this in somehow?

I wonder if the function is even necessary. My goal is to create a
hierarchical query that displays the number of children, grandchildren,
etc. And, depending on the taxonomic level, it might display the result
as "20 families, 74 genera and 413 species." With MySQL I could probably
turn that into a series of echo values, which I could then display like
this:

echo ''.$NumberChildren.' families
'.$NumberGrandchildren.' genera
'.$NumberGreatgrandchildren.' species';

I'm wondering if I should figure out this query or go back to square one
with a simpler query. I've never seen a query with a function before. ;)

Thanks for the tips.


Shoot, I should have read this before responding to the first one.

Yes, create function tax_rank, should be done in pgAdmin.

 > I wonder if the function is even necessary.

Correct, its not.  It should probably be a lookup table:

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


-Andy






Humm, after looking at this further, my answer isn't right.  I did not 
notice rank (classes, orders, families...) is different than taxon 
(mammilia, carnivora, ...)


But still, lookup table is better than function.

-Andy


--
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] Hierarchical Query Question (PHP)

[Andy Colson]

On 10/30/2015 3:47 PM, David Blomstrom wrote:

No, I get the same T_FUNCTION error.

Someone commented that the function...

   create function tax_rank(id integer) returns text as $$
 select case id
  when 1 then 'Classes'
  when 2 then 'Orders'
  when 3 then 'Families'
  when 4 then 'Genera'
  when 5 then 'Species'
end;
   $$ language sql;

...should ideally be part of the table schema. Does that mean I need to
go into pgAdmin, open up my table and paste this in somehow?

I wonder if the function is even necessary. My goal is to create a
hierarchical query that displays the number of children, grandchildren,
etc. And, depending on the taxonomic level, it might display the result
as "20 families, 74 genera and 413 species." With MySQL I could probably
turn that into a series of echo values, which I could then display like
this:

echo ''.$NumberChildren.' families
'.$NumberGrandchildren.' genera
'.$NumberGreatgrandchildren.' species';

I'm wondering if I should figure out this query or go back to square one
with a simpler query. I've never seen a query with a function before. ;)

Thanks for the tips.


Shoot, I should have read this before responding to the first one.

Yes, create function tax_rank, should be done in pgAdmin.

> I wonder if the function is even necessary.

Correct, its not.  It should probably be a lookup table:

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


-Andy




--
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] Hierarchical Query Question (PHP)

[Andy Colson]

On 10/29/2015 7:18 PM, David Blomstrom wrote:

Can anyone tell me how to write the query described @
http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query
?

The answer's very thorough, but I don't know how to string two queries
and a function together like that. This doesn't work:

$sql = "select * from gz_life_mammals;";

create function tax_rank(id integer) returns text as $$
 select case id
  when 1 then 'Classes'
  when 2 then 'Orders'
  when 3 then 'Families'
  when 4 then 'Genera'
  when 5 then 'Species'
end;
$$ language sql;

$sql = "with recursive hier(taxon,parent_id) as (
   select m.taxon, null::integer
   from gz_life_mammals m
   where taxon='Mammalia' --<< substitute me
   union all
   select m.taxon, m.parent_id
   from hier, gz_life_mammals m
   where m.parent=hier.taxon
)
select tax_rank(parent_id),
count(*) num_of_desc
from   hier
where  parent_id is not null
group by parent_id
order by parent_id;";

Thanks.



The function is created once (like with your create tables).  Don't use 
it in PHP.


Your PHP should only be like:

> $sql = "with recursive hier(taxon,parent_id) as (
>select m.taxon, null::integer
>from gz_life_mammals m
>where taxon='Mammalia' --<< substitute me
>union all
>select m.taxon, m.parent_id
>from hier, gz_life_mammals m
>where m.parent=hier.taxon
> )
> select tax_rank(parent_id),
> count(*) num_of_desc
> from   hier
> where  parent_id is not null
> group by parent_id
> order by parent_id;";

$result = pg_query($dbh, $sql);

while ($row = pg_fetch_array($result)) {
   etc
   etc


-Andy



--
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] Hierarchical Query Question (PHP)

[David Blomstrom]

No, I get the same T_FUNCTION error.

Someone commented that the function...

  create function tax_rank(id integer) returns text as $$
select case id
 when 1 then 'Classes'
 when 2 then 'Orders'
 when 3 then 'Families'
 when 4 then 'Genera'
 when 5 then 'Species'
   end;
  $$ language sql;

...should ideally be part of the table schema. Does that mean I need to go
into pgAdmin, open up my table and paste this in somehow?

I wonder if the function is even necessary. My goal is to create a
hierarchical query that displays the number of children, grandchildren,
etc. And, depending on the taxonomic level, it might display the result as
"20 families, 74 genera and 413 species." With MySQL I could probably turn
that into a series of echo values, which I could then display like this:

echo ''.$NumberChildren.' families
'.$NumberGrandchildren.' genera
'.$NumberGreatgrandchildren.' species';

I'm wondering if I should figure out this query or go back to square one
with a simpler query. I've never seen a query with a function before. ;)

Thanks for the tips.

Re: [GENERAL] pgxs/config/missing is... missing

[Jim Nasby]

On 10/30/15 2:55 PM, David E. Wheeler wrote:

On Oct 30, 2015, at 11:38 AM, Jim Nasby  wrote:


Given what pgTap's Makefile is using perl for, perhaps the best bet is to just 
ignore whatever PGXS has to say about it.


So add a check to see if it ends in “missing perl”? Suggested Makefile-foo for 
that?


Hrm, you're probably stuck with egrep for that. :( It'd be similar to 
how test and GE91 work at 
https://github.com/BlueTreble/variant/blob/master/Makefile#L18.


Though, maybe there's something that could be put together with what's 
at https://www.gnu.org/software/make/manual/html_node/Text-Functions.html.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [GENERAL] pgxs/config/missing is... missing

[David E. Wheeler]

On Oct 30, 2015, at 11:38 AM, Jim Nasby  wrote:

> Given what pgTap's Makefile is using perl for, perhaps the best bet is to 
> just ignore whatever PGXS has to say about it.

So add a check to see if it ends in “missing perl”? Suggested Makefile-foo for 
that?

D



smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] Configure Different Databases on One Server

[Jim Nasby]

On 10/29/15 5:01 PM, Thomas Kellerer wrote:

So no harm in keeping it enabled - plus this smells like premature
optimization.
I would not touch this unless you _really_ see a performance problem
that is cause by autovacuum on that database.


Moreso, if you think the problem is autovacuum (in this case) it's 
probably something else.


If I can toot my own horn, you might want to watch the recording of 
http://www.pgcon.org/2015/schedule/events/829.en.html.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [GENERAL] Domain check constraint not honored?

[Jim Nasby]

On 10/30/15 12:50 PM, Eric Schwarzenbach wrote:

On 10/30/2015 09:53 AM, Jim Nasby wrote:

On 10/29/15 5:29 PM, Eric Schwarzenbach wrote:

I'm just now converting that path to use a custom domain (along with
custom operators) instead of just being a string. (The custom operators
allow the paths to be sorted properly without each 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]+$')


Have you looked at using int[]? It wouldn't be hard to go between that
and the string representation using string_to_array() and
array_to_string(). There's also a chance that eventually you'd be able
to do FKs on it.

Do you mean making the column int[] and converting to string if needed,
or converting the string column to int[] for the purposes of the
ordering algorithm?


Changing the storage would probably be best because int[] should be 
significantly smaller than a string representation.



I did consider making the column int[] instead of a string, and it would
probably be slightly more efficient in a few ways. My main hesitations
were having to revisit the code that puts together this path, and


You could create a writable view (with the same name as the table) that 
would convert the string representation to an array using 
string_to_array(blah, '.'). That should be pretty fast.



compatibility (at the moment we're only using PostgreSQL but we've had
to run on other databases for certain clients in the past, and in theory
are open to that in the future). I realize the compatibility concern is
a little humorous in light of having gone down the
custom-operator-for-sorting route, but I can always fall back to 0 padding.


BTW, IIRC ORDER BY int[] will do what you want here.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [GENERAL] pgxs/config/missing is... missing

[Jim Nasby]

On 10/30/15 11:13 AM, Alvaro Herrera wrote:

David E. Wheeler wrote:

On Oct 29, 2015, at 7:22 PM, Jim Nasby  wrote:


I'm not sure if this is the right way to go about it, but this patch at least 
installs the file.


Which seems like a decent idea. I’d like a way to know when Perl is missing, 
though. What does `missing` do?


It prints an error message "such-and-such utility is missing".


Given what pgTap's Makefile is using perl for, perhaps the best bet is 
to just ignore whatever PGXS has to say about it.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [GENERAL] Domain check constraint not honored?

[Eric Schwarzenbach]

On 10/30/2015 09:53 AM, Jim Nasby wrote:

On 10/29/15 5:29 PM, Eric Schwarzenbach wrote:

I'm just now converting that path to use a custom domain (along with
custom operators) instead of just being a string. (The custom operators
allow the paths to be sorted properly without each 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]+$')


Have you looked at using int[]? It wouldn't be hard to go between that 
and the string representation using string_to_array() and 
array_to_string(). There's also a chance that eventually you'd be able 
to do FKs on it.
Do you mean making the column int[] and converting to string if needed, 
or converting the string column to int[] for the purposes of the 
ordering algorithm?


I did consider making the column int[] instead of a string, and it would 
probably be slightly more efficient in a few ways. My main hesitations 
were having to revisit the code that puts together this path, and 
compatibility (at the moment we're only using PostgreSQL but we've had 
to run on other databases for certain clients in the past, and in theory 
are open to that in the future). I realize the compatibility concern is 
a little humorous in light of having gone down the 
custom-operator-for-sorting route, but I can always fall back to 0 padding.



--
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] mysql_fdw trouble

[Dane Foster]

On Fri, Oct 30, 2015 at 1:02 PM, Adrian Klaver 
wrote:

> On 10/30/2015 09:55 AM, Dane Foster wrote:
>
>> On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 10/30/2015 09:36 AM, Dane Foster wrote:
>>
>> On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>
>> > >> wrote:
>>
>>  On 10/30/2015 08:13 AM, Dane Foster wrote:
>>
>>
>>  On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
>>  > 
>> > >
>>  > 
>>  > >
>>   On 10/30/2015 07:21 AM, Dane Foster wrote:
>>
>>
>>   On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
>>   > 
>>  > >
>>  > 
>>  > >>
>>   > 
>>  > >
>>
>>   > 
>>  >  wrote:
>>
>>On 10/29/2015 05:38 PM, Dane Foster wrote:
>>
>>Hello,
>>
>>I think I've tripped over another
>> mysq_fdw
>>  bug. I've
>>   filed a bug
>>report
>>on github already but just in case
>> the problem
>>  is w/ my
>>   query I
>>figured
>>I would post it here in case someone
>> sees
>>  something
>>   obvious.
>>
>>The error message I get is: null
>> value in column
>>   "location" violates
>>not-null constraint.
>>
>>The DDL is here:
>> https://github.com/EnterpriseDB/mysql_fdw/issues/71
>>
>>For the record I know top posting is
>> a crime
>>  against
>>   god and
>>humanity
>>but I feel justified because this
>> post is not
>>  directly
>>   related
>>to the
>>original. So there!  Granted it's in
>> the same
>>  milieu;
>>   and yes this
>>current sentence exists for the sole
>> purpose of me
>>   being able to
>>use the
>>word milieu because the opportunity
>> to use it
>>  is so few
>>   and far
>>between.
>>
>>​INSERT INTO series (cid, day, title,
>> description,
>>   location,
>>duration,
>>can_join)
>>SELECT
>>  cid,
>>  row_number() OVER (PARTITION BY
>> cid ORDER BY
>>   lower(duration)),
>>  title,
>>  description,
>>  location,
>>  duration,
>>  can_join
>>FROM (
>>  SELECT
>>cid,
>>title,
>>description,
>>can_joi

Re: [GENERAL] mysql_fdw trouble

[Adrian Klaver]

On 10/30/2015 09:55 AM, Dane Foster wrote:

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

On 10/30/2015 09:36 AM, Dane Foster wrote:

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

 On 10/30/2015 08:13 AM, Dane Foster wrote:


 On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
 mailto:adrian.kla...@aklaver.com>
>
 
 
 >
 
 >>
  
 >

  
  wrote:

   On 10/29/2015 05:38 PM, Dane Foster wrote:

   Hello,

   I think I've tripped over another
mysq_fdw
 bug. I've
  filed a bug
   report
   on github already but just in case
the problem
 is w/ my
  query I
   figured
   I would post it here in case someone sees
 something
  obvious.

   The error message I get is: null
value in column
  "location" violates
   not-null constraint.

   The DDL is here:
https://github.com/EnterpriseDB/mysql_fdw/issues/71

   For the record I know top posting is
a crime
 against
  god and
   humanity
   but I feel justified because this
post is not
 directly
  related
   to the
   original. So there!  Granted it's in
the same
 milieu;
  and yes this
   current sentence exists for the sole
purpose of me
  being able to
   use the
   word milieu because the opportunity
to use it
 is so few
  and far
   between.

   ​INSERT INTO series (cid, day, title,
description,
  location,
   duration,
   can_join)
   SELECT
 cid,
 row_number() OVER (PARTITION BY
cid ORDER BY
  lower(duration)),
 title,
 description,
 location,
 duration,
 can_join
   FROM (
 SELECT
   cid,
   title,
   description,
   can_join::BOOLEAN,
   (SELECT label FROM _locations
WHERE
  loc=location) AS
   location,
   ('[' || starts || ', ' ||
(starts +
 INTERVAL '4
  HOUR') ||
   ']')::TSZ_PERIOD AS duration
  

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver 
wrote:

> On 10/30/2015 09:36 AM, Dane Foster wrote:
>
>> On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 10/30/2015 08:13 AM, Dane Foster wrote:
>>
>>
>> On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>
>> > >> wrote:
>>
>>  On 10/30/2015 07:21 AM, Dane Foster wrote:
>>
>>
>>  On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
>>  > 
>> > >
>>  > 
>>
>>  > >
>>   On 10/29/2015 05:38 PM, Dane Foster wrote:
>>
>>   Hello,
>>
>>   I think I've tripped over another mysq_fdw
>> bug. I've
>>  filed a bug
>>   report
>>   on github already but just in case the problem
>> is w/ my
>>  query I
>>   figured
>>   I would post it here in case someone sees
>> something
>>  obvious.
>>
>>   The error message I get is: null value in column
>>  "location" violates
>>   not-null constraint.
>>
>>   The DDL is here:
>> https://github.com/EnterpriseDB/mysql_fdw/issues/71
>>
>>   For the record I know top posting is a crime
>> against
>>  god and
>>   humanity
>>   but I feel justified because this post is not
>> directly
>>  related
>>   to the
>>   original. So there!  Granted it's in the same
>> milieu;
>>  and yes this
>>   current sentence exists for the sole purpose of
>> me
>>  being able to
>>   use the
>>   word milieu because the opportunity to use it
>> is so few
>>  and far
>>   between.
>>
>>   ​INSERT INTO series (cid, day, title,
>> description,
>>  location,
>>   duration,
>>   can_join)
>>   SELECT
>> cid,
>> row_number() OVER (PARTITION BY cid ORDER
>> BY
>>  lower(duration)),
>> title,
>> description,
>> location,
>> duration,
>> can_join
>>   FROM (
>> SELECT
>>   cid,
>>   title,
>>   description,
>>   can_join::BOOLEAN,
>>   (SELECT label FROM _locations WHERE
>>  loc=location) AS
>>   location,
>>   ('[' || starts || ', ' || (starts +
>> INTERVAL '4
>>  HOUR') ||
>>   ']')::TSZ_PERIOD AS duration
>> FROM
>>   _series
>>   ) AS v​
>>
>>   ​Regards,​
>>
>>
>>   So what do you get when you do?:
>>
>>   SELECT
>>  cid,
>>  title,
>>  description,
>>  can_join::BOOLEAN,
>>  (SELECT label FROM _locations WHERE
>> loc=location) AS
>>  location,
>>  ('[' || starts || ', ' || (starts + INTERVAL
>> '4
>>  HOUR') ||
>>   ']')::TSTZRANGE AS duration
>>FROM
>>  _series
>>  );
>>
>>
>>   Dane
>>
>>
>>
>>
>>   --
>>   Adrian Klaver
>> adrian.kla...@aklaver.com 
>> > >
>>  > 

Re: [GENERAL] mysql_fdw trouble

[Adrian Klaver]

On 10/30/2015 09:36 AM, Dane Foster wrote:

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

On 10/30/2015 08:13 AM, Dane Foster wrote:


On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>
>> wrote:

 On 10/30/2015 07:21 AM, Dane Foster wrote:


 On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
 mailto:adrian.kla...@aklaver.com>
>
 

 
>
 

 >>

 ​
 I get rows of data, location and all.


 And when you do?:

 SELECT
  cid,
  row_number() OVER (PARTITION BY cid ORDER BY
lower(duration)),
  title,
  description,
  location,

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver 
wrote:

> On 10/30/2015 08:13 AM, Dane Foster wrote:
>
>>
>> On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 10/30/2015 07:21 AM, Dane Foster wrote:
>>
>>
>> On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>
>> >
>> >> wrote:
>>
>>  On 10/29/2015 05:38 PM, Dane Foster wrote:
>>
>>  Hello,
>>
>>  I think I've tripped over another mysq_fdw bug. I've
>> filed a bug
>>  report
>>  on github already but just in case the problem is w/ my
>> query I
>>  figured
>>  I would post it here in case someone sees something
>> obvious.
>>
>>  The error message I get is: null value in column
>> "location" violates
>>  not-null constraint.
>>
>>  The DDL is here:
>> https://github.com/EnterpriseDB/mysql_fdw/issues/71
>>
>>  For the record I know top posting is a crime against
>> god and
>>  humanity
>>  but I feel justified because this post is not directly
>> related
>>  to the
>>  original. So there!  Granted it's in the same milieu;
>> and yes this
>>  current sentence exists for the sole purpose of me
>> being able to
>>  use the
>>  word milieu because the opportunity to use it is so few
>> and far
>>  between.
>>
>>  ​INSERT INTO series (cid, day, title, description,
>> location,
>>  duration,
>>  can_join)
>>  SELECT
>>cid,
>>row_number() OVER (PARTITION BY cid ORDER BY
>> lower(duration)),
>>title,
>>description,
>>location,
>>duration,
>>can_join
>>  FROM (
>>SELECT
>>  cid,
>>  title,
>>  description,
>>  can_join::BOOLEAN,
>>  (SELECT label FROM _locations WHERE
>> loc=location) AS
>>  location,
>>  ('[' || starts || ', ' || (starts + INTERVAL '4
>> HOUR') ||
>>  ']')::TSZ_PERIOD AS duration
>>FROM
>>  _series
>>  ) AS v​
>>
>>  ​Regards,​
>>
>>
>>  So what do you get when you do?:
>>
>>  SELECT
>> cid,
>> title,
>> description,
>> can_join::BOOLEAN,
>> (SELECT label FROM _locations WHERE loc=location) AS
>> location,
>> ('[' || starts || ', ' || (starts + INTERVAL '4
>> HOUR') ||
>>  ']')::TSTZRANGE AS duration
>>   FROM
>> _series
>> );
>>
>>
>>  Dane
>>
>>
>>
>>
>>  --
>>  Adrian Klaver
>> adrian.kla...@aklaver.com 
>> >
>> >
>>
>> ​
>> I get rows of data, location and all.
>>
>>
>> And when you do?:
>>
>> SELECT
>>  cid,
>>  row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
>>  title,
>>  description,
>>  location,
>>  duration,
>>  can_join
>>FROM (
>>  SELECT
>>cid,
>>title,
>>description,
>>can_join::BOOLEAN,
>>(SELECT label FROM _locations WHERE loc=location) AS location,
>>('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
>> ']')::TSTZRANGE AS duration
>>  FROM
>>_series
>>) AS v
>>
>>
>>
>> Dane
>> ​
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>> ​Before I answer your second query question I need to revise my response
>> to the first. Yes the first query runs w/o an error message but the bit
>> about "rows and all" was not entirely correct. Out of 313 rows only the
>> first row had a location. The other 312 rows have NULL in the location
>> column which is not supposed to happen. To verify this I changed the
>> table names and removed the PostgreSQL transformations (i.e., use of ||
>> and :: for casting) and ran the query against the MySQL databas

Re: [GENERAL] mysql_fdw trouble

[Adrian Klaver]

On 10/30/2015 08:13 AM, Dane Foster wrote:


On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 10/30/2015 07:21 AM, Dane Foster wrote:


On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>
>> wrote:

 On 10/29/2015 05:38 PM, Dane Foster wrote:

 Hello,

 I think I've tripped over another mysq_fdw bug. I've
filed a bug
 report
 on github already but just in case the problem is w/ my
query I
 figured
 I would post it here in case someone sees something
obvious.

 The error message I get is: null value in column
"location" violates
 not-null constraint.

 The DDL is here:
https://github.com/EnterpriseDB/mysql_fdw/issues/71

 For the record I know top posting is a crime against
god and
 humanity
 but I feel justified because this post is not directly
related
 to the
 original. So there!  Granted it's in the same milieu;
and yes this
 current sentence exists for the sole purpose of me
being able to
 use the
 word milieu because the opportunity to use it is so few
and far
 between.

 ​INSERT INTO series (cid, day, title, description,
location,
 duration,
 can_join)
 SELECT
   cid,
   row_number() OVER (PARTITION BY cid ORDER BY
lower(duration)),
   title,
   description,
   location,
   duration,
   can_join
 FROM (
   SELECT
 cid,
 title,
 description,
 can_join::BOOLEAN,
 (SELECT label FROM _locations WHERE
loc=location) AS
 location,
 ('[' || starts || ', ' || (starts + INTERVAL '4
HOUR') ||
 ']')::TSZ_PERIOD AS duration
   FROM
 _series
 ) AS v​

 ​Regards,​


 So what do you get when you do?:

 SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS
location,
('[' || starts || ', ' || (starts + INTERVAL '4
HOUR') ||
 ']')::TSTZRANGE AS duration
  FROM
_series
);


 Dane




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

​
I get rows of data, location and all.


And when you do?:

SELECT
 cid,
 row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
 title,
 description,
 location,
 duration,
 can_join
   FROM (
 SELECT
   cid,
   title,
   description,
   can_join::BOOLEAN,
   (SELECT label FROM _locations WHERE loc=location) AS location,
   ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSTZRANGE AS duration
 FROM
   _series
   ) AS v



Dane
​



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


​Before I answer your second query question I need to revise my response
to the first. Yes the first query runs w/o an error message but the bit
about "rows and all" was not entirely correct. Out of 313 rows only the
first row had a location. The other 312 rows have NULL in the location
column which is not supposed to happen. To verify this I changed the
table names and removed the PostgreSQL transformations (i.e., use of ||
and :: for casting) and ran the query against the MySQL database; it
returned 313 rows of data, location and all.


You would think that would also cause an issue with the first row that 
is returned correctly. My suspicion is with this:


row_number() OVER (PARTITION BY cid ORDER BY lower(duration))

What happens if you run the full SELECT without it?




Now that I've cleared that up.

Your second query also runs w/o any error messages but like the first
only the first row has a non NULL value in the location column.



Dane
​




--
Adrian Klave

Re: [GENERAL] pgxs/config/missing is... missing

[Alvaro Herrera]

David E. Wheeler wrote:
> On Oct 29, 2015, at 7:22 PM, Jim Nasby  wrote:
> 
> > I'm not sure if this is the right way to go about it, but this patch at 
> > least installs the file.
> 
> Which seems like a decent idea. I’d like a way to know when Perl is missing, 
> though. What does `missing` do?

It prints an error message "such-and-such utility is missing".

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] pgxs/config/missing is... missing

[David E. Wheeler]

On Oct 29, 2015, at 7:22 PM, Jim Nasby  wrote:

> I'm not sure if this is the right way to go about it, but this patch at least 
> installs the file.

Which seems like a decent idea. I’d like a way to know when Perl is missing, 
though. What does `missing` do?

D

smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver 
wrote:

> On 10/30/2015 07:21 AM, Dane Foster wrote:
>
>>
>> On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
>> mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 10/29/2015 05:38 PM, Dane Foster wrote:
>>
>> Hello,
>>
>> I think I've tripped over another mysq_fdw bug. I've filed a bug
>> report
>> on github already but just in case the problem is w/ my query I
>> figured
>> I would post it here in case someone sees something obvious.
>>
>> The error message I get is: null value in column "location"
>> violates
>> not-null constraint.
>>
>> The DDL is here:
>> https://github.com/EnterpriseDB/mysql_fdw/issues/71
>>
>> For the record I know top posting is a crime against god and
>> humanity
>> but I feel justified because this post is not directly related
>> to the
>> original. So there!  Granted it's in the same milieu; and yes this
>> current sentence exists for the sole purpose of me being able to
>> use the
>> word milieu because the opportunity to use it is so few and far
>> between.
>>
>> ​INSERT INTO series (cid, day, title, description, location,
>> duration,
>> can_join)
>> SELECT
>>   cid,
>>   row_number() OVER (PARTITION BY cid ORDER BY
>> lower(duration)),
>>   title,
>>   description,
>>   location,
>>   duration,
>>   can_join
>> FROM (
>>   SELECT
>> cid,
>> title,
>> description,
>> can_join::BOOLEAN,
>> (SELECT label FROM _locations WHERE loc=location) AS
>> location,
>> ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
>> ']')::TSZ_PERIOD AS duration
>>   FROM
>> _series
>> ) AS v​
>>
>> ​Regards,​
>>
>>
>> So what do you get when you do?:
>>
>> SELECT
>>cid,
>>title,
>>description,
>>can_join::BOOLEAN,
>>(SELECT label FROM _locations WHERE loc=location) AS location,
>>('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
>> ']')::TSTZRANGE AS duration
>>  FROM
>>_series
>>);
>>
>>
>> Dane
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>> ​
>> I get rows of data, location and all.
>>
>
> And when you do?:
>
> SELECT
> cid,
> row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
> title,
> description,
> location,
> duration,
> can_join
>   FROM (
> SELECT
>   cid,
>   title,
>   description,
>   can_join::BOOLEAN,
>   (SELECT label FROM _locations WHERE loc=location) AS location,
>   ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
> ']')::TSTZRANGE AS duration
> FROM
>   _series
>   ) AS v
>
>
>
>> Dane
>> ​
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

​Before I answer your second query question I need to revise my response to
the first. Yes the first query runs w/o an error message but the bit about
"rows and all" was not entirely correct. Out of 313 rows only the first row
had a location. The other 312 rows have NULL in the location column which
is not supposed to happen. To verify this I changed the table names and
removed the PostgreSQL transformations (i.e., use of || and :: for casting)
and ran the query against the MySQL database; it returned 313 rows of data,
location and all.

Now that I've cleared that up.

Your second query also runs w/o any error messages but like the first only
the first row has a non NULL value in the location column.



Dane
​

Re: [GENERAL] Domain check constraint not honored?

[Eric Schwarzenbach]

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

Re: [GENERAL] mysql_fdw trouble

[Adrian Klaver]

On 10/30/2015 07:21 AM, Dane Foster wrote:


On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 10/29/2015 05:38 PM, Dane Foster wrote:

Hello,

I think I've tripped over another mysq_fdw bug. I've filed a bug
report
on github already but just in case the problem is w/ my query I
figured
I would post it here in case someone sees something obvious.

The error message I get is: null value in column "location" violates
not-null constraint.

The DDL is here: https://github.com/EnterpriseDB/mysql_fdw/issues/71

For the record I know top posting is a crime against god and
humanity
but I feel justified because this post is not directly related
to the
original. So there!  Granted it's in the same milieu; and yes this
current sentence exists for the sole purpose of me being able to
use the
word milieu because the opportunity to use it is so few and far
between.

​INSERT INTO series (cid, day, title, description, location,
duration,
can_join)
SELECT
  cid,
  row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
  title,
  description,
  location,
  duration,
  can_join
FROM (
  SELECT
cid,
title,
description,
can_join::BOOLEAN,
(SELECT label FROM _locations WHERE loc=location) AS
location,
('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSZ_PERIOD AS duration
  FROM
_series
) AS v​

​Regards,​


So what do you get when you do?:

SELECT
   cid,
   title,
   description,
   can_join::BOOLEAN,
   (SELECT label FROM _locations WHERE loc=location) AS location,
   ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSTZRANGE AS duration
 FROM
   _series
   );


Dane




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

​
I get rows of data, location and all.


And when you do?:

SELECT
cid,
row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
title,
description,
location,
duration,
can_join
  FROM (
SELECT
  cid,
  title,
  description,
  can_join::BOOLEAN,
  (SELECT label FROM _locations WHERE loc=location) AS location,
  ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') || 
']')::TSTZRANGE AS duration

FROM
  _series
  ) AS v



Dane
​




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


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

Re: [GENERAL] Hierarchical Query Question (PHP)

[Jason O'Donnell]

David,

Does wrapping the transaction with BEGIN; COMMIT; work as you would expect?


$sql = "BEGIN; with recursive hier(taxon,parent_id) as (
  select m.taxon, null::integer
  from gz_life_mammals m
  where taxon='Mammalia' --<< substitute me
  union all
  select m.taxon, m.parent_id
  from hier, gz_life_mammals m
  where m.parent=hier.taxon
)
select tax_rank(parent_id),
   count(*) num_of_desc
from   hier
where  parent_id is not null
group by parent_id
order by parent_id; COMMIT;";

On Thu, Oct 29, 2015 at 8:18 PM, David Blomstrom 
wrote:

> Can anyone tell me how to write the query described @
> http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query
> ?
>
> The answer's very thorough, but I don't know how to string two queries and
> a function together like that. This doesn't work:
>
> $sql = "select * from gz_life_mammals;";
>
> create function tax_rank(id integer) returns text as $$
> select case id
>  when 1 then 'Classes'
>  when 2 then 'Orders'
>  when 3 then 'Families'
>  when 4 then 'Genera'
>  when 5 then 'Species'
>end;
> $$ language sql;
>
> $sql = "with recursive hier(taxon,parent_id) as (
>   select m.taxon, null::integer
>   from gz_life_mammals m
>   where taxon='Mammalia' --<< substitute me
>   union all
>   select m.taxon, m.parent_id
>   from hier, gz_life_mammals m
>   where m.parent=hier.taxon
> )
> select tax_rank(parent_id),
>count(*) num_of_desc
> from   hier
> where  parent_id is not null
> group by parent_id
> order by parent_id;";
>
> Thanks.
>
>


-- 
Jason O'Donnell
Crunchy Data Solutions

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver 
wrote:

> On 10/29/2015 05:38 PM, Dane Foster wrote:
>
>> Hello,
>>
>> I think I've tripped over another mysq_fdw bug. I've filed a bug report
>> on github already but just in case the problem is w/ my query I figured
>> I would post it here in case someone sees something obvious.
>>
>> The error message I get is: null value in column "location" violates
>> not-null constraint.
>>
>> The DDL is here: https://github.com/EnterpriseDB/mysql_fdw/issues/71
>>
>> For the record I know top posting is a crime against god and humanity
>> but I feel justified because this post is not directly related to the
>> original. So there!  Granted it's in the same milieu; and yes this
>> current sentence exists for the sole purpose of me being able to use the
>> word milieu because the opportunity to use it is so few and far between.
>>
>> ​INSERT INTO series (cid, day, title, description, location, duration,
>> can_join)
>>SELECT
>>  cid,
>>  row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
>>  title,
>>  description,
>>  location,
>>  duration,
>>  can_join
>>FROM (
>>  SELECT
>>cid,
>>title,
>>description,
>>can_join::BOOLEAN,
>>(SELECT label FROM _locations WHERE loc=location) AS location,
>>('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
>> ']')::TSZ_PERIOD AS duration
>>  FROM
>>_series
>>) AS v​
>>
>> ​Regards,​
>>
>
> So what do you get when you do?:
>
> SELECT
>   cid,
>   title,
>   description,
>   can_join::BOOLEAN,
>   (SELECT label FROM _locations WHERE loc=location) AS location,
>   ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
> ']')::TSTZRANGE AS duration
> FROM
>   _series
>   );
>
>
>> Dane
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
​
I get rows of data, location and all.

Dane
​

Re: [GENERAL] Domain check constraint not honored?

[Jim Nasby]

On 10/29/15 5:29 PM, Eric Schwarzenbach wrote:

I'm just now converting that path to use a custom domain (along with
custom operators) instead of just being a string. (The custom operators
allow the paths to be sorted properly without each 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]+$')


Have you looked at using int[]? It wouldn't be hard to go between that 
and the string representation using string_to_array() and 
array_to_string(). There's also a chance that eventually you'd be able 
to do FKs on it.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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

Re: [GENERAL] ftell mismatch with expected position

[Adrian Klaver]

On 10/29/2015 02:51 AM, Eelke Klein wrote:

I am getting the following warning when our program runs pg_dump.exe and
the output is in custom format and send to standard out which is
connected to a pipe (Windows platform).

pg_dump: [custom archiver] WARNING: ftell mismatch with expected
position -- ftell used


Can you show us the exact command, with arguments, that you are using?

Also what is the size of the dump file you are creating?



The output of pg_dump is received by another process that copies it (and
some other data) into a zip file. When I extract the db backup from the
zip the backup is fine and I can restore it.

I have both tried the pg_dump.exe from the 9.5 alpha and a stable 9.4
both generate this warning.
When I change the format to plain the warning goes away.
When I call pg_dump from the commandline and let it send to standard out
and redirect it to a file the warning does not occur but it does occur
when I redirect to nul.

Is there a way to prevent this warning? I would think pg_dump shouldn't
call ftell on a pipe.

Regards, Eelke



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


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

Re: [GENERAL] mysql_fdw trouble

[Adrian Klaver]

On 10/29/2015 05:38 PM, Dane Foster wrote:

Hello,

I think I've tripped over another mysq_fdw bug. I've filed a bug report
on github already but just in case the problem is w/ my query I figured
I would post it here in case someone sees something obvious.

The error message I get is: null value in column "location" violates
not-null constraint.

The DDL is here: https://github.com/EnterpriseDB/mysql_fdw/issues/71

For the record I know top posting is a crime against god and humanity
but I feel justified because this post is not directly related to the
original. So there!  Granted it's in the same milieu; and yes this
current sentence exists for the sole purpose of me being able to use the
word milieu because the opportunity to use it is so few and far between.

​INSERT INTO series (cid, day, title, description, location, duration,
can_join)
   SELECT
 cid,
 row_number() OVER (PARTITION BY cid ORDER BY lower(duration)),
 title,
 description,
 location,
 duration,
 can_join
   FROM (
 SELECT
   cid,
   title,
   description,
   can_join::BOOLEAN,
   (SELECT label FROM _locations WHERE loc=location) AS location,
   ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') ||
']')::TSZ_PERIOD AS duration
 FROM
   _series
   ) AS v​

​Regards,​


So what do you get when you do?:

SELECT
  cid,
  title,
  description,
  can_join::BOOLEAN,
  (SELECT label FROM _locations WHERE loc=location) AS location,
  ('[' || starts || ', ' || (starts + INTERVAL '4 HOUR') || 
']')::TSTZRANGE AS duration

FROM
  _series
  );



Dane





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


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

Re: [GENERAL] Unable to select a table as postgres user

[Joe Conway]

On 10/30/2015 10:27 AM, rajan wrote:
> The function is created as postgres user.
> 
> And I get a permission denied error.

If you want help solving this, I suggest you post a minimal, self
contained test case (i.e. SQL statements) which anyone can run to
reproduce your issue.

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Unable to select a table as postgres user

[rajan]

postgres owns the function

Schema  | public
Name| testuserfunction
Result data type| x
Argument data types | 
Type| normal
Security| definer
Volatility  | volatile
Owner   | postgres
Language| plpgsql
Source code | 



--
View this message in context: 
http://postgresql.nabble.com/Unable-to-select-a-table-as-postgres-user-tp5872036p5872060.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Unable to select a table as postgres user

[rajan]

The function is created as postgres user.

And I get a permission denied error.



--
View this message in context: 
http://postgresql.nabble.com/Unable-to-select-a-table-as-postgres-user-tp5872036p5872055.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Unable to select a table as postgres user

[Charles Clavadetscher]

Hi

What error do you get?

- Permission denied or table does not exist? Latter would indicate a 
problem with the search_path and you should fully qualify the table name 
in the function body.


- Did you create the function as postgres user?

Bye
Charles

On 30/10/2015 07:56, rajan wrote:

Yes. I agree that the superuser cannot be restricted with any access.

But my scenarios is, I am executing a function(VOLATILE SECURITY DEFINER)
using the superuser and it function fails with unable to select a particular
table.

At the same time, I am able to select the table as a superuser, by executing
select * from table_name;



--
View this message in context: 
http://postgresql.nabble.com/Unable-to-select-a-table-as-postgres-user-tp5872036p5872041.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





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

Re: [GENERAL] Unable to select a table as postgres user

[Pavel Stehule]

2015-10-30 7:56 GMT+01:00 rajan :

> Yes. I agree that the superuser cannot be restricted with any access.
>
> But my scenarios is, I am executing a function(VOLATILE SECURITY DEFINER)
> using the superuser and it function fails with unable to select a
> particular
> table.
>

who owns this function?



>
> At the same time, I am able to select the table as a superuser, by
> executing
> select * from table_name;
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/Unable-to-select-a-table-as-postgres-user-tp5872036p5872041.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
>