Re: [GENERAL] Full text search question: "01.Bez." --> "Erster Bezirk"

[Dane Foster]

Hello,

​
On Sat, Mar 12, 2016 at 11:40 AM, Johann Höchtl 
wrote:

> I fear I have an involved challenge concerning FTS.
>
> Assume I have the following text in a column:
>
> Graz,06.Bez.:Blah
>
> This parses as:
> SELECT alias, description, token FROM ts_debug('german',
> 'Graz,06.Bez.:Blah');
>alias   |   description   | token
> ---+-+
>  asciiword | Word, all ASCII | Graz
>  blank | Space symbols   | ,
>  host  | Host| 06.Bez
>  blank | Space symbols   | .:
>  asciiword | Word, all ASCII | Blah
>
>
> Bez. ist the abbreviation for "Bezirk" (german for ~district). 06.Bez
> means "6th district"
>
> My first problem might be that the parser identifies "06.Bez." as a host
> lexeme, but ...
>
> I already defined a synonym dictionary to enable searching for "Bezirk",
> when there is only "Bez." in the database:
>
> file: bevaddress_host.syn:
> 01.bez bezirk
> 06.bez bezirk
> 
>
>
> CREATE TEXT SEARCH DICTIONARY bevaddress_host_syn (
> TEMPLATE = synonym,
> SYNONYMS = bevaddress_host
> );
> ALTER TEXT SEARCH CONFIGURATION german ALTER MAPPING FOR host WITH
> bevaddress_host_syn, simple;
>
>
> I wonder how I can achieve to be able to search for "Erster Bezirk"
> ("First district") to match eg. "01.Bez."
>
> Thank you for your help, Johann
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

​
As of the time of writing this I haven't seen any replies to your post so
you may not be aware that an answer was provided to your specific question
in a blog. http://obartunov.livejournal.com/185579.html

Regards,​

​
​
Dane​

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 4:48 PM, Adrian Klaver 
wrote:

>
>
>>  As an example of where this leads see:
>>
>>
>> http://www.postgresql.org/message-id/7224.1452275...@sss.pgh.pa.us
>>
>> ​Thanks for the heads up. The good news is all machine access to
>> the
>> data will be via functions and views so I can inline the
>> constraint in
>> the right places. In other news, this sucks! I have no idea what
>> it
>>
>>
>> I could see moving your constraint into a per row trigger.
>>
>>
>> You'd need to basically replicate the current FK constraint setup but
>> with custom queries...you need the insert/update trigger on the main
>> table and then a insert/update/delete trigger on the referenced table to
>> ensure that actions just rejected if the relevant detail on the main
>> table isn't changed.  Then decide whether you need something like "ON
>> UPDATE/DELETE CASCADE" instead of the default 'reject' behavior.
>>
>> I take it you would need to ensure that these triggers are disabled
>> during dump/restore but am not certain on that point.
>>
>
> Well this brings up another part to Danes post(that contained the function
> definition):
>
> "Unfortunately the "type" definition can't be expressed as a primary key
> so I can't use foreign keys to enforce consistency."
>
> Not sure what exactly is meant by "type", though I suspect it is this:
> "SELECT type FROM discount_codes WHERE code ..."
>
​Type in the context that I'm using it simply means the type of thing the
function is checking to see exists​ or not. The example I gave
unfortunately had a column named type that confuses the situation but in
actually that example is the only one that actually has a column named type
involved in evaluation.



> FYI, I know type is non-reserved word, but I would avoid using it as a
> column name. I went down that path and got myself confused in a hurry:)
>
> In any case it should be pointed out that FKs do not necessarily have to
> point to PKs:
>
> http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html
>
> "The referenced columns must be the columns of a non-deferrable unique or
> primary key constraint in the referenced table"
>
​I am aware of the fact that FKs don't have to point to PKs but they do
have to point to something backed by a unique indexed and that is what
makes them unsuitable for my needs. Here is an example where foreign keys
can't play a role,  but this time as a description.

The system can send out surveys to solicit feedback. The admin can
customize the surveys and associate them w/ specific class events. The
constraint is this, once a survey has a respondent then the survey can no
longer be edited.

​I'm going to try out David's idea of using triggers to implement the more
complex constraints and inline the simpler ones in functions where
appropriate.

Regards,


Dane​

[GENERAL] Check constraints and function volatility categories

[Dane Foster]

Hello,

I'm discovering that I need to write quite a few functions for use strictly
w/ check constraints and I'm wondering if declaring the volatility category
for said functions will affect their behavior when invoked by PostgreSQL's
check constraint mechanism.

Essentially what I'm trying to figure out is if volatility categories
increase or decrease the latency of executing check constraints. I've done
some micro benchmarks but I have no experience benchmarking anything in
PostgreSQL to trust that I'm measuring the right thing. So I'm asking the
experts.

I'm running PostgreSQL 9.5 on Ubuntu Linux 15.10. I don't know if this
matters but this is my workstation which is a pretty zippy AlienWare X51 w/
16GB RAM on a Core i7-4770 processor.

Thanks,

Dane

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 3:22 PM, Dane Foster <studdu...@gmail.com> wrote:

>
> On Mon, Feb 1, 2016 at 2:41 PM, Adrian Klaver <adrian.kla...@aklaver.com>
> wrote:
>
>> On 02/01/2016 11:17 AM, Dane Foster wrote:
>>
>>> Hello,
>>>
>>> I'm discovering that I need to write quite a few functions for use
>>> strictly w/ check constraints and I'm wondering if declaring the
>>> volatility category for said functions will affect their behavior when
>>> invoked by PostgreSQL's check constraint mechanism.
>>>
>>> Essentially what I'm trying to figure out is if volatility categories
>>> increase or decrease the latency of executing check constraints. I've
>>> done some micro benchmarks but I have no experience benchmarking
>>> anything in PostgreSQL to trust that I'm measuring the right thing. So
>>> I'm asking the experts.
>>>
>>
>> The above is sort of backwards. You need to ask what the function does
>> and from that determine what is the most appropriate volatitity category.
>> For more detailed info see:
>>
>> http://www.postgresql.org/docs/9.5/interactive/xfunc-volatility.html
>>
>
> ​I did that already and all of the ones written so far would be STABLE.
> The gist of all of them is they check for the presence or absence of a
> particular type of thing to exist in some other table. Unfortunately the
> "type" definition can't be expressed as a primary key so I can't use
> foreign keys to enforce consistency.
> ​
>
>
>> It would help to see some samples of the actual functions.
>
> ​-- $1: The class event primary key
> -- $2: The discount code
> CREATE FUNCTION discounts_enabled_for(INT, CITXT70) RETURNS BOOLEAN AS $$
> SELECT NOT
>   CASE (SELECT type FROM discount_codes WHERE code = $2)
> WHEN 'giftcert'::DISC_CODE_TYPE THEN TRUE
> WHEN 'coupon'::DISC_CODE_TYPE
>   THEN (SELECT no_coupons FROM class_events WHERE cid = $1)
>   ELSE (SELECT no_vouchers FROM class_events WHERE cid = $1)
>   END;
> $$ LANGUAGE SQL STRICT LEAKPROOF;
> COMMENT ON FUNCTION discounts_enabled_for(INT, CITXT70) IS
> $$Determines if a class event accepts coupon or voucher discounts.$$;
>
> CREATE TABLE group_codes (
>   cid  INTEGER
>PRIMARY KEY
>REFERENCES class_events ON DELETE CASCADE ON UPDATE CASCADE,
>   code CITXT70
>NOT NULL
>REFERENCES discount_codes ON DELETE CASCADE ON UPDATE CASCADE,
>   CONSTRAINT discounts_enabled CHECK (discounts_enabled_for(cid, code))
> );
> CREATE INDEX ON group_codes USING GIN (code);
> COMMENT ON TABLE group_codes IS
> $$Discount codes that are exclusive to a set of class events.$$;
>

​I just realized there is little bug in the function body. So before anyone
gets distracted by it I wanted to let you know that I know it exists and
has been fixed internally.​

​
>

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 2:41 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 02/01/2016 11:17 AM, Dane Foster wrote:
>
>> Hello,
>>
>> I'm discovering that I need to write quite a few functions for use
>> strictly w/ check constraints and I'm wondering if declaring the
>> volatility category for said functions will affect their behavior when
>> invoked by PostgreSQL's check constraint mechanism.
>>
>> Essentially what I'm trying to figure out is if volatility categories
>> increase or decrease the latency of executing check constraints. I've
>> done some micro benchmarks but I have no experience benchmarking
>> anything in PostgreSQL to trust that I'm measuring the right thing. So
>> I'm asking the experts.
>>
>
> The above is sort of backwards. You need to ask what the function does and
> from that determine what is the most appropriate volatitity category. For
> more detailed info see:
>
> http://www.postgresql.org/docs/9.5/interactive/xfunc-volatility.html
>

​I did that already and all of the ones written so far would be STABLE. The
gist of all of them is they check for the presence or absence of a
particular type of thing to exist in some other table. Unfortunately the
"type" definition can't be expressed as a primary key so I can't use
foreign keys to enforce consistency.
​


> It would help to see some samples of the actual functions.

​-- $1: The class event primary key
-- $2: The discount code
CREATE FUNCTION discounts_enabled_for(INT, CITXT70) RETURNS BOOLEAN AS $$
SELECT NOT
  CASE (SELECT type FROM discount_codes WHERE code = $2)
WHEN 'giftcert'::DISC_CODE_TYPE THEN TRUE
WHEN 'coupon'::DISC_CODE_TYPE
  THEN (SELECT no_coupons FROM class_events WHERE cid = $1)
  ELSE (SELECT no_vouchers FROM class_events WHERE cid = $1)
  END;
$$ LANGUAGE SQL STRICT LEAKPROOF;
COMMENT ON FUNCTION discounts_enabled_for(INT, CITXT70) IS
$$Determines if a class event accepts coupon or voucher discounts.$$;

CREATE TABLE group_codes (
  cid  INTEGER
   PRIMARY KEY
   REFERENCES class_events ON DELETE CASCADE ON UPDATE CASCADE,
  code CITXT70
   NOT NULL
   REFERENCES discount_codes ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT discounts_enabled CHECK (discounts_enabled_for(cid, code))
);
CREATE INDEX ON group_codes USING GIN (code);
COMMENT ON TABLE group_codes IS
$$Discount codes that are exclusive to a set of class events.$$;
​

>
>
>> I'm running PostgreSQL 9.5 on Ubuntu Linux 15.10. I don't know if this
>> matters but this is my workstation which is a pretty zippy AlienWare X51
>> w/ 16GB RAM on a Core i7-4770 processor.
>>
>> Thanks,
>>
>> Dane
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 3:41 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 02/01/2016 12:36 PM, David G. Johnston wrote:
>
>> On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster <studdu...@gmail.com
>> <mailto:studdu...@gmail.com>>wrote:
>>
>>
>> On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston
>> <david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>>
>> wrote:
>>
>> On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com
>> >>wrote:
>>
>>
>> On 02/01/2016 11:17 AM, Dane Foster wrote:
>>
>> Hello,
>>
>> I'm discovering that I need to write quite a few
>> functions for use
>> strictly w/ check constraints and I'm wondering if
>> declaring the
>> volatility category for said functions will affect their
>> behavior when
>> invoked by PostgreSQL's check constraint mechanism.
>>
>>
>> ​Adrian's point is spot-on but the important thing to consider
>> in this situation is that check constraints are assumed to be
>> immutable and if you implement a check function that is not you
>> don't get to complain what you see something broken.  The nature
>> and use of an immutable check constraint only has a single
>> dynamic - execute the function using the given values once for
>> every record INSERT or UPDATE.  There is no reason, and I
>> suspect there is no actual, attempt to even look at the
>> volatility category of said function before performing those
>> actions.  It is possible that two records inserted or updated in
>> the same query could make use of the caching possibilities
>> afforded by immutable functions but if so assume it is being
>> done unconditionally.
>>
>> David J.
>>
>> ​Your point about ".. check ​constraints are assumed to be immutable
>> ..", is that in the manual? Because I don't remember reading it in
>> the constraints section, nor in the volatility categories section,
>> nor in the server programming sections. Granted, I haven't read the
>> whole manual yet nor do I have what I've read so far memorized, but
>> I think that little fact would have struck a cord in my gray matter.
>> So if you can point me to the spot in the manual where this is
>> covered I would appreciate it.​
>>
>>
>>
>> ​http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html​
>> ​Second Paragraph​
>>
>> ​"""​
>>   CHECK ( expression ) [ NO INHERIT ]
>> The CHECK clause specifies an expression producing a Boolean result
>> which new or updated rows must satisfy for an insert or update operation
>> to succeed. Expressions evaluating to TRUE or UNKNOWN succeed. Should
>> any row of an insert or update operation produce a FALSE result, an
>> error exception is raised and the insert or update does not alter the
>> database. A check constraint specified as a column constraint should
>> reference that column's value only, while an expression appearing in a
>> table constraint can reference multiple columns.
>>
>> Currently, CHECK expressions cannot contain subqueries nor refer to
>> variables other than columns of the current row. The system column
>> tableoid may be referenced, but not any other system column.
>>
>> A constraint marked with NO INHERIT will not propagate to child tables.
>>
>> When a table has multiple CHECK constraints, they will be tested for
>> each row in alphabetical order by name, after checking NOT NULL
>> constraints. (PostgreSQL versions before 9.5 did not honor any
>> particular firing order for CHECK constraints.)
>> ​"""
>>
>> While you've managed to fool the system by wrapping your query into a
>> function you've violated the documented restrictions and so any breakage
>> is on you - not the system.
>>
>
> As an example of where this leads see:
>
> http://www.postgresql.org/message-id/7224.1452275...@sss.pgh.pa.us
>
> ​Thanks for the heads up. The good news is all machine access to the data
will be via functions and views so I can inline the constraint in the right
places. In other news, this sucks! I have no idea what it would take to
implement a more flexible constraint mechanism where these types of
dependencies can be expressed declaratively but it would be great if
someone w/ the know-how did. As is evident by the fact that I wasn't the
only one to not realize the rabbit hole I was heading down, it would be a
useful feature.
​

​As always thanks for setting me straight,

Dane

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver <adrian.kla...@aklaver.com>
> wrote:
>
>> On 02/01/2016 11:17 AM, Dane Foster wrote:
>>
>>> Hello,
>>>
>>> I'm discovering that I need to write quite a few functions for use
>>> strictly w/ check constraints and I'm wondering if declaring the
>>> volatility category for said functions will affect their behavior when
>>> invoked by PostgreSQL's check constraint mechanism.
>>>
>>
> ​Adrian's point is spot-on but the important thing to consider in this
> situation is that check constraints are assumed to be immutable and if you
> implement a check function that is not you don't get to complain what you
> see something broken.  The nature and use of an immutable check constraint
> only has a single dynamic - execute the function using the given values
> once for every record INSERT or UPDATE.  There is no reason, and I suspect
> there is no actual, attempt to even look at the volatility category of said
> function before performing those actions.  It is possible that two records
> inserted or updated in the same query could make use of the caching
> possibilities afforded by immutable functions but if so assume it is being
> done unconditionally.
>
> David J.
>
> ​Your point about ".. check ​constraints are assumed to be immutable ..",
is that in the manual? Because I don't remember reading it in the
constraints section, nor in the volatility categories section, nor in the
server programming sections. Granted, I haven't read the whole manual yet
nor do I have what I've read so far memorized, but I think that little fact
would have struck a cord in my gray matter. So if you can point me to the
spot in the manual where this is covered I would appreciate it.​

Thanks,

Dane

Re: [GENERAL] Check constraints and function volatility categories

[Dane Foster]

On Mon, Feb 1, 2016 at 3:36 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Mon, Feb 1, 2016 at 1:30 PM, Dane Foster <studdu...@gmail.com> wrote:
>
>>
>> On Mon, Feb 1, 2016 at 2:56 PM, David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Mon, Feb 1, 2016 at 12:41 PM, Adrian Klaver <
>>> adrian.kla...@aklaver.com> wrote:
>>>
>>>> On 02/01/2016 11:17 AM, Dane Foster wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>> I'm discovering that I need to write quite a few functions for use
>>>>> strictly w/ check constraints and I'm wondering if declaring the
>>>>> volatility category for said functions will affect their behavior when
>>>>> invoked by PostgreSQL's check constraint mechanism.
>>>>>
>>>>
>>> ​Adrian's point is spot-on but the important thing to consider in this
>>> situation is that check constraints are assumed to be immutable and if you
>>> implement a check function that is not you don't get to complain what you
>>> see something broken.  The nature and use of an immutable check constraint
>>> only has a single dynamic - execute the function using the given values
>>> once for every record INSERT or UPDATE.  There is no reason, and I suspect
>>> there is no actual, attempt to even look at the volatility category of said
>>> function before performing those actions.  It is possible that two records
>>> inserted or updated in the same query could make use of the caching
>>> possibilities afforded by immutable functions but if so assume it is being
>>> done unconditionally.
>>>
>>> David J.
>>>
>>> ​Your point about ".. check ​constraints are assumed to be immutable
>> ..", is that in the manual? Because I don't remember reading it in the
>> constraints section, nor in the volatility categories section, nor in the
>> server programming sections. Granted, I haven't read the whole manual yet
>> nor do I have what I've read so far memorized, but I think that little fact
>> would have struck a cord in my gray matter. So if you can point me to the
>> spot in the manual where this is covered I would appreciate it.​
>>
>>
>>
> ​http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html​
> ​Second Paragraph​
>
> ​"""​
>  CHECK ( expression ) [ NO INHERIT ]
> The CHECK clause specifies an expression producing a Boolean result which
> new or updated rows must satisfy for an insert or update operation to
> succeed. Expressions evaluating to TRUE or UNKNOWN succeed. Should any row
> of an insert or update operation produce a FALSE result, an error exception
> is raised and the insert or update does not alter the database. A check
> constraint specified as a column constraint should reference that column's
> value only, while an expression appearing in a table constraint can
> reference multiple columns.
>
> Currently, CHECK expressions cannot contain subqueries nor refer to
> variables other than columns of the current row. The system column tableoid
> may be referenced, but not any other system column.
>
> A constraint marked with NO INHERIT will not propagate to child tables.
>
> When a table has multiple CHECK constraints, they will be tested for each
> row in alphabetical order by name, after checking NOT NULL constraints.
> (PostgreSQL versions before 9.5 did not honor any particular firing order
> for CHECK constraints.)
> ​"""
>
> While you've managed to fool the system by wrapping your query into a
> function you've violated the documented restrictions and so any breakage is
> on you - not the system.
>
> Also, consider that at the time you insert a row the check constraint
> passes but then you alter the other table so that, if you tried to insert
> the row again it would fail.  Since check constraints are only evaluated
> upon INSERT/UPDATE of the data on the same table you would have a violation.
>
> So, while the documentation doesn't explicitly say that functions used in
> CHECK must be IMMUTABLE that is what it all boils down to when you put all
> of these things together.
>
> David J.
>
> ​Though I understand the thinking you have applied to conclude that a
CHECK constraint is supposed to be IMMUTABLE I don't necessarily agree w/
it nor has the section you quoted made that expectation clear. Because when
I read it the first time and even again now it is not immediately apparent
that that assumption exists. But if it is true, as in, that is the intent
of the code then it should be made explicit in the documentation.

​Regards,​

​Dane​

[GENERAL] PL/pgSQL debugger

[Dane Foster]

Hello,

I googled "plpgsql debugger" and eventually ended up at
http://pgfoundry.org/projects/edb-debugger/, where the latest release is
almost 8 years old. I am aware that this is not an EDB forum but given that
I sent them an e-mail at 9AM EST and I haven't received a response yet I
figured it can't hurt to try my question here. Is anyone using it on recent
(e.g., 9.5) versions of PostgreSQL. If not, is there any consensus on what
one should be using to debug PL/pgSQL functions?

Thanks,

Dane

Re: [GENERAL] PL/pgSQL debugger

[Dane Foster]

On Fri, Jan 29, 2016 at 4:22 PM, Melvin Davidson <melvin6...@gmail.com>
wrote:

> You did not indicate which O/S you are using, but here are the detailed
> instructions for Windows.
>
>
> http://blog.databasepatterns.com/2014/01/installing-debugger-for-pgadmin-on.html
>
> Note that the most important part is to load the .dll in postgresql.conf.
>
> shared_preload_libraries = '$libdir/plugin_debugger.dll'
>
> This should also work for Linux, providing you have the .dll
>
> On Fri, Jan 29, 2016 at 4:02 PM, Pavel Stehule <pavel.steh...@gmail.com>
> wrote:
>
>> Hi
>>
>> 2016-01-29 21:52 GMT+01:00 Dane Foster <studdu...@gmail.com>:
>>
>>> Hello,
>>>
>>> I googled "plpgsql debugger" and eventually ended up at
>>> http://pgfoundry.org/projects/edb-debugger/, where the latest release
>>> is almost 8 years old. I am aware that this is not an EDB forum but given
>>> that I sent them an e-mail at 9AM EST and I haven't received a response yet
>>> I figured it can't hurt to try my question here. Is anyone using it on
>>> recent (e.g., 9.5) versions of PostgreSQL. If not, is there any consensus
>>> on what one should be using to debug PL/pgSQL functions?
>>>
>>
>> plpgsql debugger should be included directly in pgAdminIII
>>
>> The necessary extension can 8 years old - it implements communication
>> between client and server side code and this is long time without change
>>
>>  http://www.pgadmin.org/docs/1.8/debugger.html
>>
>> Regards
>>
>> Pavel
>>
>>
>>> Thanks,
>>>
>>> Dane
>>>
>>
>>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>
​My workstation and laptop are Ubuntu Linux 15.10.​

[GENERAL] A contradiction in 13.2.1

[Dane Foster]

Hello,

I'm trying to understand concurrency in PostgreSQL so I'm slowly reading
through chapter 13 of the fine manual and I believe I've found a
contradiction in section 13.2.1.

​My understanding of the second sentence of the first paragraph is that
read committed mode never sees "changes committed during query execution by
concurrent transactions". For example let's assume two transactions, A & B,
and the following:

   - A started before B
   - B starts before A commits

My understanding of the second sentence means that if A commits before B
then any updates made by A will continue to be invisible to B because B's
snapshot was before A committed. Now if I'm wrong about this then there is
no contradiction forthcoming.
The final sentence of the first paragraph is where I find the
contradiction. It says: "Also note that two successive SELECT commands can
see different data, even though they are within a single transaction, if
other transactions commit changes after the first SELECT starts and before
the second SELECT starts​"
​.

So the mental model I've built based on the first four sentences of the
first paragraph is that when a transaction starts in read committed mode a
snapshot is taken of the (database) universe as it exists at the moment of
its creation and that it's only updated by changes made by the transaction
that created the snapshot. So for successive SELECTs to see different data
because of updates outside of the transaction that created the snapshot is
a contradiction.

Now my guess is that I'm thinking about it all wrong so if someone in the
know could shed some light on where/how my mental model breaks down I would
appreciate it.

Regards,

Dane

Re: [GENERAL] A contradiction in 13.2.1

[Dane Foster]

On Wed, Jan 27, 2016 at 5:59 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Jan 27, 2016 at 3:23 PM, Dane Foster <studdu...@gmail.com> wrote:
>
>> Hello,
>>
>> I'm trying to understand concurrency in PostgreSQL so I'm slowly reading
>> through chapter 13 of the fine manual and I believe I've found a
>> contradiction in section 13.2.1.
>>
>> ​My understanding of the second sentence of the first paragraph is that
>> read committed mode never sees "changes committed during query execution by
>> concurrent transactions". For example let's assume two transactions, A & B,
>> and the following:
>>
>>- A started before B
>>- B starts before A commits
>>
>> My understanding of the second sentence means that if A commits before B
>> then any updates made by A will continue to be invisible to B because B's
>> snapshot was before A committed. Now if I'm wrong about this then there is
>> no contradiction forthcoming.
>> The final sentence of the first paragraph is where I find the
>> contradiction. It says: "Also note that two successive SELECT commands
>> can see different data, even though they are within a single transaction,
>> if other transactions commit changes after the first SELECT starts and
>> before the second SELECT starts​"
>> ​.
>>
>> So the mental model I've built based on the first four sentences of the
>> first paragraph is that when a transaction starts in read committed mode a
>> snapshot is taken of the (database) universe as it exists at the moment of
>> its creation and that it's only updated by changes made by the transaction
>> that created the snapshot. So for successive SELECTs to see different data
>> because of updates outside of the transaction that created the snapshot is
>> a contradiction.
>>
>> Now my guess is that I'm thinking about it all wrong so if someone in the
>> know could shed some light on where/how my mental model breaks down I would
>> appreciate it.
>>
>> Regards,
>>
>> Dane
>>
>
> ​The main thing to remember is that "query != transaction".​
>
> A1 - BEGIN;
> ​A1 - SELECT FROM a
> B1 - BEGIN;
> B2 - UPDATE a
> B3 - COMMIT;
> A2 - SELECT FROM a - again
> A3 - COMMIT;
>
> Since the commit in B3 occurs before the second select A2 in READ
> COMMITTED the query A2 *will see* the update made in B2.  But B3 must
> complete in its entirety for A2 to see it otherwise "it never sees [...]
> changes committed during query execution by concurrent transactions".  The
> concurrency is with the individual statement A2 and not the entire A
> transaction.  This is why it is called "READ COMMITTED" because within
> transaction A externally committed data is able to be read.
>
> David J.
>
>
> ​You are correct, I was conflating query w/ transaction. But it's clear
now. Thank you​
​
​.​


Dane

​

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

[Dane Foster]

On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus  wrote:

>
>
> > Am I on the right track, or is there some better way to set this up?  My
> > understanding is that views really aren't meant for insert/update
> > operations, and I have seen on the web that using views to insert/update
> is
> > a bit tricky - and still requires a procedure with a rule on the view.
>
> Why not use updatable CTEs?  That's what they're for.
>
> WITH update_contact as (
>INSERT INTO contacts ( contact_id, name )
>VALUES ( nexval('contacts_id_seq'), 'Joe' )
>RETURNING contact_id ),
> new_cont_ids AS (
>SELECT contact_id FROM update_contact;
> ),
> insert_phones AS (
>INSERT INTO phones ( phone_id, contact_id, phone_no )
>SELECT ( nextval('phone_id_seq'), contact_id, '415-555-1212' )
>FROM new_cont_ids
>RETURNING phone_id
> ) ...
>
> I think you get the idea.  On 9.3 or later, this is the way to go.
>
> --
> Josh Berkus
> Red Hat OSAS
> (opinions are my own)
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
​
In general do updateable CTEs have lower overhead than functions?

Dane
​

[GENERAL] Can PostgreSQL use multi-column index for FK constraint validation?

[Dane Foster]

Hello,

If I have a primary key index of the form:
(col1, col2, col3)
and a foreign key constraint of the form:
FOREIGN KEY (col1, col2) REFERENCES foo
 ON DELETE CASCADE ON UPDATE CASCADE
should I create a separate index (col1, col2) or is PostgreSQL capable of
using the primary key's index?

Thanks,

Dane

Re: [GENERAL] Can PostgreSQL use multi-column index for FK constraint validation?

[Dane Foster]

On Tue, Jan 26, 2016 at 3:15 PM, Josh Berkus <j...@agliodbs.com> wrote:

> On 01/26/2016 11:38 AM, Dane Foster wrote:
> > Hello,
> >
> > If I have a primary key index of the form:
> > (col1, col2, col3)
> > and a foreign key constraint of the form:
> > FOREIGN KEY (col1, col2) REFERENCES foo
> >  ON DELETE CASCADE ON UPDATE CASCADE
> > should I create a separate index (col1, col2) or is PostgreSQL capable
> > of using the primary key's index?
>
> You are not required to create one.
>
> foo(col1, col2) needs a unique index.  There need not be any specific
> index on (col1, col2) in the referencing table.  Whether you want one
> for performance depends on how selective (col1, col2) is without col3,
> and how large the table is.
>
> --
> Josh Berkus
> Red Hat OSAS
> (opinions are my own)
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
​
My example is modeling an order details table and the answer to the
question of selectivity is it depends. For some of our clients it is highly
selective because customers generally order a single item at a time. For
others it's multi-modal because it starts out w/ their customers ordering
only a single item but over time customer behavior changes and there is
this mix of single and multi item orders. Additionally my use case for
PostgreSQL is the VPS use case where each client has their own schema so
I'd prefer not to have to deal w/ per client index building and
maintenance. So is there a rule of thumb design wise for variable
selectivity as I've described?



Dane
​

Re: [GENERAL] A motion

[Dane Foster]

​
On Sun, Jan 24, 2016 at 4:00 PM, bret_stern <
bret_st...@machinemanagement.com> wrote:

> Adrian,
> I hope you reconsider. You have far more value to the list.
> The CoC dictators will flame out, then where will we be.
> Just sit on the sidelines until the show is
> over.
> Look forward to the next awesome year.
> My CoC: "keep it technical"
>
> Fore
>
​
+1 To Adrian sticking around. I'm relatively new to participating on this
list and PostgreSQL in general and you've been extremely helpful to me
personally in answering questions I've raised and providing
guidance/suggestions. I'm no fan of the CoC conversation either so I scan
then delete and go on w/ my day. It's a strategy that is working for me and
I hope you will adopt it and stick around. Newbies like myself need people
like on this list.

Regards,
​
Dane
​

Re: [GENERAL] Enforcing referential integrity against a HSTORE column

[Dane Foster]

On Sat, Jan 2, 2016 at 12:19 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 01/02/2016 08:13 AM, Dane Foster wrote:
>
> Ccing list.
>
>> On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 01/01/2016 07:47 PM, Dane Foster wrote:
>>
>> Hello,
>>
>> I'm moving a MySQL database to PostgreSQL and redesigning parts
>> of it to
>> take advantage of PostgreSQL's richer type system and other
>> advance
>> features. Currently I am attempting to replace a table of
>> name/value
>> pair data w/ a hstore column. But now that the data will no
>> longer be
>> flattened out in a table I need to manually handle referential
>> integrity
>>
>>
>> And the benefit is?
>> ​
>>
>> ​
>> The benefit is supposed to be client side simplicity. The data in these
>> particular tables are ultimately consumed by JavaScript as JSON on the
>> front end to populate/maintain a dynamic HTML for​m. So I was attempting
>> to build a model that more closely reflects how the data is used because
>> the people using the data aren't SQL folks and the code that converts
>> the data from table/rows to JSON is not straight forward for my audience.
>>
>
> In that case you may want to look at the JSON types, json and/or
> jsonb(depending on Postgres version):
>
>
> http://www.postgresql.org/docs/9.4/interactive/datatype-json.html
>
> ​That's exactly what I did. The json_object_agg function sealed the deal.​

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


Dane
​

Re: [GENERAL] Enforcing referential integrity against a HSTORE column

[Dane Foster]

On Sat, Jan 2, 2016 at 10:59 AM, Bill Moran 
wrote:

> On Sat, 2 Jan 2016 07:30:38 -0800
> Adrian Klaver  wrote:
>
> > > So given:
> > >
> > > CREATE TABLE xtra_fields(
> > >xfk SERIAL PRIMARY KEY,
> > >xtk INTEGER NOT NULL REFERENCES xtra_types,
> > >...
> > > );
> > >
> > > CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$
> > > WITH keyz AS (SELECT skeys($1)::INT AS xfk)
> > > SELECT
> > >(SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk))
> > >=
> > >(SELECT COUNT(*) FROM keyz)
> > > $$LANGUAGE SQL STABLE STRICT LEAKPROOF;
> > >
> > > CREATE TABLE foo(
> > >id INTEGER NOT NULL CHECK (id > 0),
> > >...
> > > -- Extra fields where the keys are the xtra_fields.xfk values and the
> > > values are the
> > > -- data values for the specific xfk.
> > >xtra hstore CHECK (foo_xtra_fk(xtra))
> > > );
> > >
> > > is ?there a more efficient way of maintaining logical referential
> integrity?
>
> I second Adrian's comment on making sure that the benefit of HSTORE is
> outweighing the drawback of having to write your own checks ... however,
> if you decide that HSTORE is the right way to go, you may want to try
> something more along the lines of this for your check:​



> SELECT true WHERE NOT EXISTS(SELECT 1 FROM keyz WHERE xfk NOT IN
> (akeys($1)));
>

​​I also agree w/ Adrian's comments and to that end I will be keeping the
original tables and building a view that does what I need on top of them.
But I really like your SQL solution. It is quite elegant and I suspect it
would run faster than mine for a large enough data set. So I'll be filing
it away in memory for use at some future point in time.
​


>
> Not tested, so it's possible that I have some typo or something; but
> overall
> I've found that the NOT EXISTS construct can be very efficient in cases
> like these.
>
> --
> Bill Moran
>
​
Thanks,

Dane
​

[GENERAL] Enforcing referential integrity against a HSTORE column

[Dane Foster]

Hello,

I'm moving a MySQL database to PostgreSQL and redesigning parts of it to
take advantage of PostgreSQL's richer type system and other advance
features. Currently I am attempting to replace a table of name/value pair
data w/ a hstore column. But now that the data will no longer be flattened
out in a table I need to manually handle referential integrity

So given:

CREATE TABLE xtra_fields(
  xfk SERIAL PRIMARY KEY,
  xtk INTEGER NOT NULL REFERENCES xtra_types,
  ...
);

CREATE OR REPLACE FUNCTION foo_xtra_fk(HSTORE) RETURNS BOOLEAN AS $$
WITH keyz AS (SELECT skeys($1)::INT AS xfk)
SELECT
  (SELECT COUNT(*) FROM keyz JOIN xtra_fields USING (xfk))
  =
  (SELECT COUNT(*) FROM keyz)
$$LANGUAGE SQL STABLE STRICT LEAKPROOF;

CREATE TABLE foo(
  id INTEGER NOT NULL CHECK (id > 0),
  ...
-- Extra fields where the keys are the xtra_fields.xfk values and the
values are the
-- data values for the specific xfk.
  xtra hstore CHECK (foo_xtra_fk(xtra))
);

is ​there a more efficient way of maintaining logical referential integrity?


Thank you for your consideration,​

Dane

[GENERAL] Check constraint and at least two rows

[Dane Foster]

Hello,

I have a design/modelling puzzle/problem. I'm trying to model a series of
events. So I have two tables w/ a parent child relationship. The child
table has the rule/constraint/etc that for every row in the parent table
there must be at least 2 rows in the child because a series must have at
least 2 events to be a series.

Now the SQL for the constraint is straight-forward and easy to write. What
I haven't figure out yet is where to put it because a straight forward
table constraint won't work because it's checked on every INSERT which
means it will be tripped on the first row inserted. A trigger doesn't seem
to fit the bill either.

Ideas?

Thanks,

Dane

Re: [GENERAL] Check constraint and at least two rows

[Dane Foster]

On Tue, Nov 3, 2015 at 7:09 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, Nov 3, 2015 at 4:55 PM, Dane Foster <studdu...@gmail.com> wrote:
>
>> Hello,
>>
>> I have a design/modelling puzzle/problem. I'm trying to model a series of
>> events. So I have two tables w/ a parent child relationship. The child
>> table has the rule/constraint/etc that for every row in the parent table
>> there must be at least 2 rows in the child because a series must have at
>> least 2 events to be a series.
>>
>> Now the SQL for the constraint is straight-forward and easy to write.
>> What I haven't figure out yet is where to put it because a straight forward
>> table constraint won't work because it's checked on every INSERT which
>> means it will be tripped on the first row inserted. A trigger doesn't seem
>> to fit the bill either.
>>
>> Ideas?
>>
>>
> ​http://www.postgresql.org/docs/devel/static/sql-createtrigger.html
> ​
>
> ​"""​
> In contrast, a trigger that is marked FOR EACH STATEMENT only executes
> once for any given operation, regardless of how many rows it modifies (in
> particular, an operation that modifies zero rows will still result in the
> execution of any applicable FOR EACH STATEMENT triggers
> ​.
> """
>
> ​That said while the "perfect" model may indeed conform to your definition
> as a practical matter what harm would there be in allowing zero or one
> child records for a given parent?  Usually problems stem from designing a
> "zero-or-one" setup and then realizing that you actually have a "as many as
> you want" situation.​  Allowing a "as many as you want" setup to choose
> zero or one is significantly less problematic though you do need to be more
> aware of the need for LEFT JOINs.
>
David J.
>
> ​
I hear ya but ... what I'm working on is the migration of an existing
application from MySQL to PostgreSQL. And one of the things I've been
playing w/ recently is the mysql_fdw to do the migration of the data.​ I've
already encountered cases where implicit business rules have been violated
because there is no generalized constraint mechanism in MySQL (e.g., NOT
NULL doesn't count!) to make them explicit. So since PostgreSQL has a
generalized constraint mechanism (because NOT NULL doesn't get you very
far) I'd like to take advantage of it and eliminate the possibility of
developers screwing up my data model and data. Because I have the luxury of
knowing what ALL the requirements are I don't have to play the "I need to
be flexible" game. The game I want to play is, "ha you can't introduced
logical inconsistencies into my data model!"

Dane
​

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver <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.

Dane
​

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver <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
>> <adrian.kla...@aklaver.com <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 <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,
> 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] mysql_fdw trouble

[Dane Foster]

On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver <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
>> <adrian.kla...@aklaver.com <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
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>>
>> <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 <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>>
>> <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,
>>  duration,
>>  can_join
>>FROM (
>>  SELECT
>>cid,
>>title,
>>description,
>>  

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Fri, Oct 30, 2015 at 1:02 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 10/30/2015 09:55 AM, Dane Foster wrote:
>
>> On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver
>> <adrian.kla...@aklaver.com <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
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>>     <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
>>  <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>
>>  <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>
>>  <mailto:adrian.kla...@aklaver.com
>> <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
>>   <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>
>>  <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>
>>  <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>
>>  <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>>
>>   <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>
>>      <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>
>>
>>   <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>
>>  <mailto:adrian.kla...@aklaver.com
>> <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,
>>

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver <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
>> <adrian.kla...@aklaver.com <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
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>> <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
>>  <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>
>>  <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>
>>
>>  <mailto:adrian.kla...@aklaver.com
>> <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,
>>

[GENERAL] mysql_fdw trouble

[Dane Foster]

Hello,

I have a MySQL/PHP app that I want to port to PostgreSQL so I just
installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw
because I'd like to do the data migration in SQL if possible.

Installation and set up worked flawlessly but when I run the following query
SELECT
  title,
  description,
  '[' || starts || ', ' || COALESCE(ends, 'infinity') || ']'
FROM
  _filler
WHERE
  starts IS NOT NULL
  AND description IS NOT NULL
  AND LENGTH(TRIM(title)) > 0
  AND LENGTH(TRIM(description)) > 0;

​I get the following error:​
​ ​

​ERROR:  failed to prepare the MySQL query:
FUNCTION latest.btrim does not exist

FYI: Tables names that start w/ _ are the MySQL versions of PostgreSQL
tables.​


​Any help would be appreciated.

Thanks,
​

Dane

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Thu, Oct 29, 2015 at 1:56 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 10/29/2015 10:47 AM, Dane Foster wrote:
>
>> Hello,
>>
>> I have a MySQL/PHP app that I want to port to PostgreSQL so I just
>> installed the mysql_fdw from https://github.com/EnterpriseDB/mysql_fdw
>> because I'd like to do the data migration in SQL if possible.
>>
>> Installation and set up worked flawlessly but when I run the following
>> query
>> SELECT
>>title,
>>description,
>>'[' || starts || ', ' || COALESCE(ends, 'infinity') || ']'
>> FROM
>>_filler
>> WHERE
>>starts IS NOT NULL
>>AND description IS NOT NULL
>>AND LENGTH(TRIM(title)) > 0
>>AND LENGTH(TRIM(description)) > 0;
>>
>> ​I get the following error:​
>> ​ ​
>>
>> ​ERROR:  failed to prepare the MySQL query:
>> FUNCTION latest.btrim does not exist
>>
>> FYI: Tables names that start w/ _ are the MySQL versions of PostgreSQL
>> tables.​
>>
>>
>> ​Any help would be appreciated.
>>
>
> What version of Postgres are you using?
>
>
>> Thanks,
>> ​
>>
>> Dane
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
​
My bad. I should know better. It's PostgreSQL 9.5beta1 on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.9.2-10ubuntu13) 4.9.2, 64-bit

Thanks,

Dane
​

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Dane Foster <studdu...@gmail.com> writes:
> > Installation and set up worked flawlessly but when I run the following
> query
> > ...
> > ​I get the following error:​
> > ​ERROR:  failed to prepare the MySQL query:
> > FUNCTION latest.btrim does not exist
>
> It looks like mysql_fdw is messing up by sending the trim() checks for
> remote execution when there is no suitable function on the remote side.
> Don't know whether that's a bug in mysql_fdw, or whether there's some
> setup you're supposed to perform on the mysql server and have omitted.
>
> regards, tom lane
>
​I think you are correct about mysql_fdw "... sending the trim() checks for
remote execution" because according to the docs:

"The latest version will push-down the foreign table where clause to the
foreign server. The where condition on the foreign table will be executed
on the foreign server hence there will be fewer rows to to bring across to
PostgreSQL. This is a performance feature."
I guess using mysql_fdw is a no-go for my data migration needs.


Dane
​

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 10/29/2015 12:10 PM, Dane Foster wrote:
>
>> On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce <pie...@hogranch.com
>> <mailto:pie...@hogranch.com>> wrote:
>>
>> On 10/29/2015 11:20 AM, Dane Foster wrote:
>>
>>> ​I think you are correct about mysql_fdw "... sending the trim()
>>> checks for remote execution" because according to the docs:
>>>
>>> "The latest version will push-down the foreign table where clause
>>> to the foreign server. The where condition on the foreign table
>>> will be executed on the foreign server hence there will be fewer
>>> rows to to bring across to PostgreSQL. This is a performance
>>> feature."
>>>
>>>
>> the alternative would be to fetch the whole table across the FDW
>> interface, then run the where locally, for a large table where
>> you're only selecting a few rows, this would be very painful.
>>
>> I guess using mysql_fdw is a no-go for my data migration needs.
>>>
>>
>> or, rewrite that WHERE clause to be mysql compatible.
>>
>> Easier said than done because the LENG​TH and TRIM functions both exist
>> in MySQL but I guess under the covers in PostgreSQL btrim is being
>> invoked when TRIM is called therefore that is what is being "pushed
>> down" to the MySQL and there is nothing I can do about that.
>>
>> I guess I could leave out the call to trim, and copy the data into a
>> temp table on the PostgreSQL side, and blah blah blah. My point being
>> why should I have to jump through hoops because mysql_fdw is broken?
>> I'll just go back to writing the migration script as a PHP program
>> because if mysql_fdw didn't exist that's what I would have to do anyway.
>>
>
> Remember you are using a Beta version of Postgres, so it is not entirely
> unexpected that things might be broken, especially when working with
> non-core extensions. In the spirit of testing, that Beta implies, why not
> help fix mysql_fdw by filing an issue? If you already have, my apologies.
>
​I'm fully aware of that fact and gladly accept my responsibility which is
why I have opened an issue:
https://github.com/EnterpriseDB/mysql_fdw/issues/70

For me reporting the issue in the hopes that they will fix it is a separate
issue from expending energy working around the bug because the great thing
about the procedural code is that it's littered w/ the same SQL that a pure
SQL migration script would contain. So if they fix it in reasonable amount
of time then all that's required to create a pure SQL migration script is
copy/paste.

Dane​

>
>>
>> --
>> john r pierce, recycling bits in santa cruz
>>
>> ​Dane​
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce <pie...@hogranch.com> wrote:

> On 10/29/2015 11:20 AM, Dane Foster wrote:
>
> ​I think you are correct about mysql_fdw "... sending the trim() checks
> for remote execution" because according to the docs:
>
> "The latest version will push-down the foreign table where clause to the
> foreign server. The where condition on the foreign table will be executed
> on the foreign server hence there will be fewer rows to to bring across to
> PostgreSQL. This is a performance feature."
>
>
> the alternative would be to fetch the whole table across the FDW
> interface, then run the where locally, for a large table where you're only
> selecting a few rows, this would be very painful.
>
> I guess using mysql_fdw is a no-go for my data migration needs.
>
>
> or, rewrite that WHERE clause to be mysql compatible.
>
Easier said than done because the LENG​TH and TRIM functions both exist in
MySQL but I guess under the covers in PostgreSQL btrim is being invoked
when TRIM is called therefore that is what is being "pushed down" to the
MySQL and there is nothing I can do about that.

I guess I could leave out the call to trim, and copy the data into a temp
table on the PostgreSQL side, and blah blah blah. My point being why should
I have to jump through hoops because mysql_fdw is broken? I'll just go back
to writing the migration script as a PHP program because if mysql_fdw
didn't exist that's what I would have to do anyway.


>
> --
> john r pierce, recycling bits in santa cruz
>
> ​Dane​

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

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

Dane

On Thu, Oct 29, 2015 at 4:37 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 10/29/2015 12:56 PM, Dane Foster wrote:
>
>> On Thu, Oct 29, 2015 at 3:30 PM, Adrian Klaver
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 10/29/2015 12:10 PM, Dane Foster wrote:
>>
>> On Thu, Oct 29, 2015 at 3:01 PM, John R Pierce
>> <pie...@hogranch.com <mailto:pie...@hogranch.com>
>> <mailto:pie...@hogranch.com <mailto:pie...@hogranch.com>>> wrote:
>>
>>  On 10/29/2015 11:20 AM, Dane Foster wrote:
>>
>>  ​I think you are correct about mysql_fdw "... sending
>> the trim()
>>  checks for remote execution" because according to the
>> docs:
>>
>>  "The latest version will push-down the foreign table
>> where clause
>>  to the foreign server. The where condition on the
>> foreign table
>>  will be executed on the foreign server hence there will
>> be fewer
>>  rows to to bring across to PostgreSQL. This is a
>> performance feature."
>>
>>
>>  the alternative would be to fetch the whole table across
>> the FDW
>>  interface, then run the where locally, for a large table
>> where
>>  you're only selecting a few rows, this would be very painful.
>>
>>  I guess using mysql_fdw is a no-go for my data
>> migration needs.
>>
>>
>>  or, rewrite that WHERE clause to be mysql compatible.
>>
>> Easier said than done because the LENG​TH and TRIM functions
>> both exist
>> in MySQL but I guess under the covers in PostgreSQL btrim is being
>> invoked when TRIM is called therefore that is what is being
>> "pushed
>> down" to the MySQL and there is nothing I can do about that.
>>
>> I guess I could leave out the call to trim, and copy the data
>> into a
>> temp table on the PostgreSQL side, and blah blah blah. My point
>> being
>> why should I have to jump through hoops because mysql_fdw is
>> broken?
>> I'll just go back to writing the migration script as a PHP program
>> because if mysql_fdw didn't exist that's what I would have to do
>> anyway.
>>
>>
>> Remember you are using a Beta version of Postgres, so it is not
>> entirely unexpected that things might be broken, especially when
>> working with non-core extensions. In the spirit of testing, that
>> Beta implies, why not help fix mysql_fdw by filing an issue? If you
>> already have, my apologies.
>>
>> ​I'm fully aware of that fact and gladly accept my responsibility which
>> is why I have opened an
>> issue:https://github.com/EnterpriseDB/mysql_fdw/issues/70
>>
>
> Great and thanks.
>
>
>> For me reporting the issue in the hopes that they will fix it is a
>> separate issue from expending energy working around the bug because the
>> great thing about the procedural code is that it's littered w/ the same
>> SQL that a pure SQL migration script would contain. So if they fix it in
>> reasonable amount of time then all that's required to create a pure SQL
>> migration script is copy/paste.
>>
>
>
>
>> Dane​
>>
>>
>>
>>  --
>>  john r pierce, recycling bits in santa cruz
>>
>> ​Dane​
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Domain check constraint not honored?

[Dane Foster]

On Thu, Oct 29, 2015 at 7:16 PM, Rob Sargent  wrote:

> 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.]+
>

​If I understand your regex needs correctly you want to allow digits
separated by dots (like IPv4 octets) but never start w/ or end w/ a dot nor
any non digit character other than a dot. If that's the case this may work.
I say may because I'm using PCRE syntax and I don't know how much of it
PostgreSQL supports.

^(?>\d+)(?>\.\d+)*$

If there is no support for atomic groups you can try this:
^(?:\d++)(?:\.\d++)*$

And if there is no support for greedy quantifiers nor non capturing groups:
^(\d+)(\.\d++)*$

I hoped that helped.

Good luck,

Dane

​

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Thu, Oct 29, 2015 at 8:22 PM, CaT <c...@zip.com.au> wrote:

> On Thu, Oct 29, 2015 at 08:38:49PM -0400, Dane Foster wrote:
> > 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
>
> Then it should be a new thread or the old post should not be quoted.
>
> So there! ;)
>
> --
>   "A search of his car uncovered pornography, a homemade sex aid, women's
>   stockings and a Jack Russell terrier."
> -
> http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480
>
​
Well played sir!

Dane
​

Re: [GENERAL] mysql_fdw trouble

[Dane Foster]

On Thu, Oct 29, 2015 at 2:20 PM, Dane Foster <studdu...@gmail.com> wrote:

> On Thu, Oct 29, 2015 at 2:04 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
>> Dane Foster <studdu...@gmail.com> writes:
>> > Installation and set up worked flawlessly but when I run the following
>> query
>> > ...
>> > ​I get the following error:​
>> > ​ERROR:  failed to prepare the MySQL query:
>> > FUNCTION latest.btrim does not exist
>>
>> It looks like mysql_fdw is messing up by sending the trim() checks for
>> remote execution when there is no suitable function on the remote side.
>> Don't know whether that's a bug in mysql_fdw, or whether there's some
>> setup you're supposed to perform on the mysql server and have omitted.
>>
>> regards, tom lane
>>
> ​I think you are correct about mysql_fdw "... sending the trim() checks
> for remote execution" because according to the docs:
>
> "The latest version will push-down the foreign table where clause to the
> foreign server. The where condition on the foreign table will be executed
> on the foreign server hence there will be fewer rows to to bring across to
> PostgreSQL. This is a performance feature."
> I guess using mysql_fdw is a no-go for my data migration needs.
>
>
> Dane
> ​
> ​
>
​I'm not sure who to direct this question to but if the root cause is
really automatic push-down what about instea​d of automatic push-down of
the WHERE clause the mysql_fdw detected when the WHERE clause contained
PostgreSQL specific functions and not push the WHERE clause to MySQL? The
docs suggest that the old version did not push the WHERE clause down which
suggests that WHERE clause processing occurred on the PostgreSQL side. So
what if that PostgreSQL side WHERE clause processing code is revived and
used in the case where the WHERE clause shouldn't be pushed down?

This is all speculation of course and I don't have the time nor expertise
to go hacking on this idea. So I won't be offended if no one thinks it's a
good idea nor volunteers to write the code.

Dane​

Re: [GENERAL] partial JOIN (was: ID column naming convention)

[Dane Foster]

On Sat, Oct 24, 2015 at 3:42 PM, Rafal Pietrak  wrote:

>
>
> W dniu 24.10.2015 o 21:03, Rafal Pietrak pisze:
> >
> >
> > W dniu 24.10.2015 o 15:00, David G. Johnston pisze:
> >> On Sat, Oct 24, 2015 at 6:41 AM, Rafal Pietrak  >> >wrote:
> > [--]
> >>
> >> ​Using explicit column names is expected - using "*" in non-trivial and
> >> production queries is not.
> >>
> >> You can move the aliases if you would like.
> >>
> >> SELECT *
> >> FROM tablea (col1, col2, col4)
> >> JOIN tableb AS tb1 (col1, col3, col5) USING (col1)
> >> JOIN tableb AS tb2​
> >>
> >> ​(col1, col6, col7) USING (col1)
> >
> > I knew there must have been something like this.
>
> Upss. Almost, but not quite. I've just read the manual on that
> (http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html)
> and it looks like "col1", "col2", etc in the above example are column
> *aliases*. Right?
>
> So I have to list *all* the columns of the aliased table irrespectively
> if I need any of them within the output, or not.
>
> It's a pity standard didn't choose to make column aliasing optional,
> allowing for cherry pick what's aliased like following:
>
> .. JOIN table AS tb(column7 [as alias1], column3 [as alias2],...)
>
> thenx anyway, "Mandatory" column aliasing is helpfull too.
>
> -R
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
​
You may be able to accomplish that using aliased sub-selects as in-line
views. The purpose of the sub-selects in this use-case is simply to cherry
pick the columns you want.
SELECT *
FROM
  (SELECT col1, col2, col4 FROM tablea) AS iv
  JOIN (SELECT co1, col3, col5 FROM tableb) AS tb1 USING (col1))
  JOIN (SELECT col1, col6, col7 FROM tableb) AS tb22 USING (col1)

Please note, this may be a performance nightmare for large tables because
w/o a WHERE clause that can be pushed down to the sub-selects each
sub-select will do a full table scan.

Please note that the 3rd JOIN clause is nutty (I translated it from your
original) because why would you join a table to itself just to select a
different set of columns?

Good luck,

Dane

​

Re: [GENERAL] partial JOIN (was: ID column naming convention)

[Dane Foster]

On Sat, Oct 24, 2015 at 5:23 PM, Dane Foster <studdu...@gmail.com> wrote:

> On Sat, Oct 24, 2015 at 3:42 PM, Rafal Pietrak <ra...@ztk-rp.eu> wrote:
>
>>
>>
>> W dniu 24.10.2015 o 21:03, Rafal Pietrak pisze:
>> >
>> >
>> > W dniu 24.10.2015 o 15:00, David G. Johnston pisze:
>> >> On Sat, Oct 24, 2015 at 6:41 AM, Rafal Pietrak <ra...@ztk-rp.eu
>> >> <mailto:ra...@ztk-rp.eu>>wrote:
>> > [--]
>> >>
>> >> ​Using explicit column names is expected - using "*" in non-trivial and
>> >> production queries is not.
>> >>
>> >> You can move the aliases if you would like.
>> >>
>> >> SELECT *
>> >> FROM tablea (col1, col2, col4)
>> >> JOIN tableb AS tb1 (col1, col3, col5) USING (col1)
>> >> JOIN tableb AS tb2​
>> >>
>> >> ​(col1, col6, col7) USING (col1)
>> >
>> > I knew there must have been something like this.
>>
>> Upss. Almost, but not quite. I've just read the manual on that
>> (http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html
>> )
>> and it looks like "col1", "col2", etc in the above example are column
>> *aliases*. Right?
>>
>> So I have to list *all* the columns of the aliased table irrespectively
>> if I need any of them within the output, or not.
>>
>> It's a pity standard didn't choose to make column aliasing optional,
>> allowing for cherry pick what's aliased like following:
>>
>> .. JOIN table AS tb(column7 [as alias1], column3 [as alias2],...)
>>
>> thenx anyway, "Mandatory" column aliasing is helpfull too.
>>
>> -R
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
> ​
> You may be able to accomplish that using aliased sub-selects as in-line
> views. The purpose of the sub-selects in this use-case is simply to cherry
> pick the columns you want.
> SELECT *
> FROM
>   (SELECT col1, col2, col4 FROM tablea) AS iv
>   JOIN (SELECT co1, col3, col5 FROM tableb) AS tb1 USING (col1))
>   JOIN (SELECT col1, col6, col7 FROM tableb) AS tb22 USING (col1)
>
> Please note, this may be a performance nightmare for large tables because
> w/o a WHERE clause that can be pushed down to the sub-selects each
> sub-select will do a full table scan.
>
> Please note that the 3rd JOIN clause is nutty (I translated it from your
> original) because why would you join a table to itself just to select a
> different set of columns?
>
> Good luck,
>
> Dane
>
​
For the record SELECT * in my example is absolutely the wrong thing to do
but your original didn't leave me w/ any other option.

Dane
​

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

[Dane Foster]

On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <jim.na...@bluetreble.com> wrote:

> On 10/21/15 9:32 PM, Dane Foster wrote:
>
>> "If STRICT is not specified in the INTO clause, then target will be
>> set to the first row returned by the query, or to nulls if the query
>> returned no rows."
>>
>> ​Foot removed from mouth.
>>
>
> Note however that there's some unexpected things when checking whether a
> record variable IS (NOT) NULL. It's not as simple as 'has the variable been
> set or not'.
> --
> 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
>

​​Please elaborate. I'm entirely new to PL/pgSQL so the more details you
can provide the better.​
​
Thanks,

Dane
​

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

[Dane Foster]

On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro <thomas.mu...@enterprisedb.com
> wrote:

> On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdu...@gmail.com> wrote:
> > On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <jim.na...@bluetreble.com>
> wrote:
> >> On 10/21/15 9:32 PM, Dane Foster wrote:
> >>>
> >>> "If STRICT is not specified in the INTO clause, then target will be
> >>> set to the first row returned by the query, or to nulls if the
> query
> >>> returned no rows."
> >>>
> >>> Foot removed from mouth.
> >>
> >> Note however that there's some unexpected things when checking whether a
> >> record variable IS (NOT) NULL. It's not as simple as 'has the variable
> been
> >> set or not'.
> >
> > Please elaborate. I'm entirely new to PL/pgSQL so the more details you
> can
> > provide the better.
> > Thanks,
>
> The surprising thing here, required by the standard, is that this
> expression is true:
>
>   ROW(NULL, NULL) IS NULL
>
> So "r IS NULL" is not a totally reliable way to check if your row
> variable was set or not by the SELECT INTO, if there is any chance
> that r is a record full of NULL.  "r IS NOT DISTINCT FROM NULL" would
> work though, because it's only IS [NOT] NULL that has that strange
> special case.  Other constructs that have special behaviour for NULL
> don't consider a composite type composed of NULLs to be NULL.  For
> example IS DISTINCT FROM, COALESCE, COUNT, STRICT functions.
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>
​
Someone should include your explanation in the [fine] manual.

Dane​

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

[Dane Foster]

On Thu, Oct 22, 2015 at 9:15 PM, Thomas Munro <thomas.mu...@enterprisedb.com
> wrote:

> On Fri, Oct 23, 2015 at 12:33 PM, Dane Foster <studdu...@gmail.com> wrote:
> > On Thu, Oct 22, 2015 at 6:54 PM, Thomas Munro
> > <thomas.mu...@enterprisedb.com> wrote:
> >>
> >> On Fri, Oct 23, 2015 at 10:27 AM, Dane Foster <studdu...@gmail.com>
> wrote:
> >> > On Thu, Oct 22, 2015 at 2:00 PM, Jim Nasby <jim.na...@bluetreble.com>
> >> > wrote:
> >> >> On 10/21/15 9:32 PM, Dane Foster wrote:
> >> >>>
> >> >>> "If STRICT is not specified in the INTO clause, then target will
> >> >>> be
> >> >>> set to the first row returned by the query, or to nulls if the
> >> >>> query
> >> >>> returned no rows."
> >> >>>
> >> >>> Foot removed from mouth.
> >> >>
> >> >> Note however that there's some unexpected things when checking
> whether
> >> >> a
> >> >> record variable IS (NOT) NULL. It's not as simple as 'has the
> variable
> >> >> been
> >> >> set or not'.
> >> >
> >> > Please elaborate. I'm entirely new to PL/pgSQL so the more details you
> >> > can
> >> > provide the better.
> >> > Thanks,
> >>
> >> The surprising thing here, required by the standard, is that this
> >> expression is true:
> >>
> >>   ROW(NULL, NULL) IS NULL
> >>
> >> So "r IS NULL" is not a totally reliable way to check if your row
> >> variable was set or not by the SELECT INTO, if there is any chance
> >> that r is a record full of NULL.  "r IS NOT DISTINCT FROM NULL" would
> >> work though, because it's only IS [NOT] NULL that has that strange
> >> special case.  Other constructs that have special behaviour for NULL
> >> don't consider a composite type composed of NULLs to be NULL.  For
> >> example IS DISTINCT FROM, COALESCE, COUNT, STRICT functions.
> >
> > Someone should include your explanation in the [fine] manual.
>
> The quirky standard behaviour of IS [NOT] NULL with rows is described
> in a 'Note' section here:
>
> http://www.postgresql.org/docs/9.4/static/functions-comparison.html
>
> But I do think we should consider pointing out explicitly that "IS
> NULL" doesn't mean the same thing as, erm, "is null" where it appears
> throughout the documentation, and I proposed a minor tweak:
>
>
> http://www.postgresql.org/message-id/CAEepm=1wW4MGBS6Hwteu6B-OMZiX6_FM=wfyn7otehycfkg...@mail.gmail.com
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>
​
It just occurred to me that another option, for my specific example, would
be to record/cache FOUND instead of testing the RECORD variable for its
NULLness. Unless of course assigning FOUND to a variable is a
pass-by-reference assignment, which in the actual code that I'm writing
would be problematic because FOUND is set many times because there are at
least 4 SQL commands that my function executes.

Dane
​

Re: [GENERAL] My first PL/pgSQL function

[Dane Foster]

On Wed, Oct 21, 2015 at 3:20 AM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2015-10-21 4:08 GMT+02:00 Dane Foster <studdu...@gmail.com>:
>
>> Since I'm switching to OUT parameters is there any difference
>> (performance/efficiency wise) between using an INTO STRICT
>> RECORD_TYPE_VARIABLE statement which forces me to copy/assign the property
>> values from the RECORD to the OUT parameter variables and simply listing
>> the OUT parameters, i.e., INTO STRICT outparam1, outparam2, ..., outparamN?
>>
>
> It strongly depends on what do you do. I artificial benchmarks you can
> find tens percent difference (based on massive cycles), but in life there
> will be zero difference probably. The bottleneck in PLpgSQL functions are
> SQL statements usually, and the overhead of "glue" is pretty less. Mainly
> if you has not any loop there.
>
> Regards
>
> Pavel
>
>
>
>>
>> Thanks,
>>
>> Dane
>>
>> On Tue, Oct 20, 2015 at 4:37 PM, Pavel Stehule <pavel.steh...@gmail.com>
>> wrote:
>>
>>>
>>>
>>> 2015-10-20 22:22 GMT+02:00 Dane Foster <studdu...@gmail.com>:
>>>
>>>> Here is the updated version w/ the feedback incorporated. I'm going to
>>>> install PostgreSQL 9.6 from source this weekend so I can start
>>>> testing/debugging. Does anyone here have any experience using the pgAdmin
>>>> debugger recently? I ask because it seems a little dated (September 26,
>>>> 2008).
>>>>
>>>>
>>>> Thanks,
>>>>
>>>> Dane
>>>>
>>>> /**
>>>>  * Returns the status of a coupon or voucher.
>>>>  * @param _code The discount code.
>>>>  * @return NULL if the discount does not exist otherwise a composite
>>>> type (see return
>>>>  * type declaration below).
>>>>
>>>>  *
>>>>  * Voucher codes have the following properties:
>>>>  * type - The type of discount (voucher, giftcert).
>>>>  *
>>>>  * status   - The status of the voucher. The valid values are:
>>>>  *void - The voucher has been voided.
>>>>  *
>>>>  *expired  - The voucher has expired.
>>>>  *
>>>>  *inactive - The gift certificate has not been sent yet.
>>>>  *
>>>>  *ok   - The voucher has been activated, has not
>>>> expired, and has a
>>>>  *   current value greater than zero.
>>>>  *
>>>>  * date - The expiration or activation or void date of the voucher
>>>> in a reader
>>>>  *friendly format.
>>>>  *
>>>>  * datetime - The expiration or activation or void date of the gift
>>>> certificate in
>>>>  *-MM-DD HH:MM:SS format.
>>>>  *
>>>>  * value- The current value of the voucher.
>>>>  *
>>>>  * The mandatory properties are type and status. The presence of the
>>>> other properties
>>>>  * are dependent on the value of status.
>>>>
>>>>  
>>>> 
>>>>  * Coupon codes can provide the following additional parameters that
>>>> are used to
>>>>  * determine if an order meets a coupon's minimum requirements.
>>>>  * @param int seats The number of seats in the user's order.
>>>>
>>>>  * @param numeric subtotal The order's subtotal.
>>>>  *
>>>>  * Coupon codes have the following properties:
>>>>  * type - The type of discount (coupon).
>>>>  *
>>>>  * status   - The status of the coupon code. The valid values are:
>>>>  *void - The coupon has been voided.
>>>>  *
>>>>  *expired  - The coupon has expired.
>>>>  *
>>>>  *inactive - The coupon has not been activated yet.
>>>>  *
>>>>  *min  - The minimum seats or dollar amount requirement
>>>> has not been
>>>>  *   met.
>>>>  *
>>>>  *ok   - The coupon can be used.
>>>>  *
>>>>  * min  - The minimum seats or dollar amount requirement. The value
>>>> of this
>>>>  *property is either an unsigned integer or dollar amount
>>>>

[GENERAL] A question about PL/pgSQL DECLAREd variable behavior

[Dane Foster]

I wrote the following simple function to try to learn what happens to a
DECLAREd variable whose assignment comes from an INTO statement where the
query being executed does not return a result.

CREATE OR REPLACE FUNCTION _test() RETURNS BOOLEAN AS $$
DECLARE r RECORD;
BEGIN SELECT 1 AS one INTO r WHERE false; RETURN r IS NULL; END;
$$ LANGUAGE plpgsql;

The function returns true. Given that I can't find any explicit reference
in the documentation about the behavior I've just described is it safe to
assume that the current behavior is the expected behavior but it's just not
documented?

Dane

Re: [GENERAL] A question about PL/pgSQL DECLAREd variable behavior

[Dane Foster]

On Wed, Oct 21, 2015 at 10:23 PM, Thomas Munro <
thomas.mu...@enterprisedb.com> wrote:

> On Thu, Oct 22, 2015 at 2:48 PM, Dane Foster <studdu...@gmail.com> wrote:
> > I wrote the following simple function to try to learn what happens to a
> > DECLAREd variable whose assignment comes from an INTO statement where the
> > query being executed does not return a result.
> >
> > CREATE OR REPLACE FUNCTION _test() RETURNS BOOLEAN AS $$
> > DECLARE r RECORD;
> > BEGIN SELECT 1 AS one INTO r WHERE false; RETURN r IS NULL; END;
> > $$ LANGUAGE plpgsql;
> >
> > The function returns true. Given that I can't find any explicit
> reference in
> > the documentation about the behavior I've just described is it safe to
> > assume that the current behavior is the expected behavior but it's just
> not
> > documented?
>
>
> http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
>
> "If STRICT is not specified in the INTO clause, then target will be
> set to the first row returned by the query, or to nulls if the query
> returned no rows."
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>

​Foot removed from mouth.

Dane​

[GENERAL] My first PL/pgSQL function

[Dane Foster]

Hello,

I'm in the very very very very early stages of migrating a MySQL/PHP app to
PostgreSQL/PHP/Lua. Because we are moving to PostgreSQL one of the [many]
things I intend to change is to move ALL the SQL code/logic out of the
application layer and into the database where it belongs. So after months
of reading the [fine] PostgreSQL manual my first experiment is to port some
PHP/SQL code to a PostgreSQL function.

At this stage the function is a purely academic exercise because like I
said before it's early days so no data has been migrated yet so I don't
have data to test it against. My reason for sharing at such an early stage
is because all I've done so far is read the [fine] manual and I'd like to
know if I've groked at least some of the material.

I would appreciate any feedback you can provide. I am particularly
interested in learning about the most efficient way to do things in
PL/pgSQL because I would hate for the first iteration of the new version of
the app to be slower than the old version.

Thank you for your consideration,

Dane

​/**
 * Returns the status of a coupon or voucher.
 * @param _code The discount code.
 * @return NULL if the discount does not exist otherwise a JSON object.
 *
 * Voucher codes have the following properties:
 * type - The type of discount (voucher, giftcert).
 *
 * status   - The status of the voucher. The valid values are:
 *void - The voucher has been voided.
 *
 *expired  - The voucher has expired.
 *
 *inactive - The gift certificate has not been sent yet.
 *
 *ok   - The voucher has been activated, has not expired,
and has a
 *   current value greater than zero.
 *
 * date - The expiration or activation or void date of the voucher in a
reader
 *friendly format.
 *
 * datetime - The expiration or activation or void date of the gift
certificate in
 *-MM-DD HH:MM:SS format.
 *
 * value- The current value of the voucher.
 *
 * The mandatory properties are type and status. The presence of the other
properties
 * are dependent on the value of status.
 

 * Coupon codes can provide the following additional parameters that are
used to
 * determine if an order meets a coupon's minimum requirements.
 * @param int seats The number of seats in the user's cart.
 * @param numeric subtotal The order's subtotal.
 *
 * Coupon codes have the following properties:
 * type - The type of discount (coupon).
 *
 * status   - The status of the coupon code. The valid values are:
 *void - The coupon has been voided.
 *
 *expired  - The coupon has expired.
 *
 *inactive - The coupon has not been activated yet.
 *
 *min  - The minimum seats or dollar amount requirement has
not been
 *   met.
 *
 *ok   - The coupon can be used.
 *
 * min  - The minimum seats or dollar amount requirement. The value of
this
 *property is either an unsigned integer or dollar amount
string w/ the
 *dollar sign.
 *
 * date - The expiration or activation or void date of the coupon in a
reader
 *friendly format.
 *
 * datetime - The expiration or activation or void date of the coupon in
-MM-DD
 * HH:MM:SS format.
 *
 * value- The current value of the coupon as a string. The value of
this property
 *is either an unsigned integer w/ a percent symbol or dollar
amount
 *string w/ the dollar sign.
 */
CREATE OR REPLACE FUNCTION check_discount_code(
  _code public.CITXT70,
  VARIADIC cpnxtra NUMERIC[]
) RETURNS JSON AS $$
DECLARE
  discount RECORD;
BEGIN

  SELECT
ok,
v.value,
created,
expires,
modified,
effective_date,
-- The minimum quantity or dollar amount required to use the coupon.
COALESCE(
  lower(qty_range),
  '$' || to_char(lower(amount_range), '999D99')
)   AS min,
CASE type::TEXT
  WHEN 'voucher'
  THEN
CASE WHEN gd.code IS NOT NULL THEN 'giftcert' END
  ELSE type::TEXT
END AS type,
to_char(expires, 'Dy, MM Mon. ')AS expd,
to_char(modified, 'Dy, MM Mon. ')   AS mdate,
to_char(effective_date, 'Dy, MM Mon. ') AS edate,
-- Determines if the coupon has been used up.
CASE WHEN maxuse > 0 THEN maxuse - used <= 0 ELSE FALSE END AS maxuse,
effective_date > CURRENT_DATE   AS notyet,
expires < CURRENT_DATE  AS expired,
-- The coupon's discount value as a dollar amount or percent.
COALESCE(
  discount_rate || '%',
  '$' || to_char(discount_amount, '999D99')
   

Re: [GENERAL] My first PL/pgSQL function

[Dane Foster]

On Tue, Oct 20, 2015 at 12:43 PM, Merlin Moncure <mmonc...@gmail.com> wrote:

> On Tue, Oct 20, 2015 at 9:45 AM, Dane Foster <studdu...@gmail.com> wrote:
> > Hello,
> >
> > I'm in the very very very very early stages of migrating a MySQL/PHP app
> to
> > PostgreSQL/PHP/Lua. Because we are moving to PostgreSQL one of the [many]
> > things I intend to change is to move ALL the SQL code/logic out of the
> > application layer and into the database where it belongs. So after
> months of
> > reading the [fine] PostgreSQL manual my first experiment is to port some
> > PHP/SQL code to a PostgreSQL function.
> >
> > At this stage the function is a purely academic exercise because like I
> said
> > before it's early days so no data has been migrated yet so I don't have
> data
> > to test it against. My reason for sharing at such an early stage is
> because
> > all I've done so far is read the [fine] manual and I'd like to know if
> I've
> > groked at least some of the material.
> >
> > I would appreciate any feedback you can provide. I am particularly
> > interested in learning about the most efficient way to do things in
> PL/pgSQL
> > because I would hate for the first iteration of the new version of the
> app
> > to be slower than the old version.
> >
> > Thank you for your consideration,
>
> This is beautiful code. It in fact is an for all intents and purposes
> an exact replica of my personal style.
>
> Some notes:
> *) I agree with Pavel; better to return specific columns if the result
> is well defined (mark them in the argument list with OUT and I tend to
> not prefix underscore them in that case).  The caller can always do a
> json production if necessary, or you can wrap the function.
>
> Some other minor suggestions:
> *) I tend to prefer format() to || concatenation in ALL usage these
> days.  It's more readable and tends to give better handling of NULL
> strings by default.
>
> *) this login should really be documented in line
>   IF 2 = array_length(cpnxtra, 1)
>   THEN
>
> *) I avoid all right justified code (spaced out AS x, AS y, etc).  I
> understand the perceived readability improvements but none of them are
> worth the cascading edits when variables get longer.
>
> *) let's compare notes on your doxygen style code markup. I've been
> trouble finding a good robust tool that does exactly what I want,
> curious if you did better.
>
> *) FYI, since you're obviously not using pgadmin, I use 'Sublime Text
> 3' for my code editor.  I've significantly enhanced it to support
> various postgresqlisms, so if you're maintaining code in a codebase,
> you have reasonable support for 'jump to definition' and things like
> that.
>
> merlin
>
​
Thank you Pavel and Merlin for the feedback. I'm delighted that my first
PL/pgSQL function wasn't rubbish. I think the credit goes to the authors of
the [fine] PostgreSQL manual.

Pavel, I've taken your recommendation to heart but I'll need to do some
more reading on composite types because I didn't realize they were on
option in this context (i.e., the fields/columns aren't fixed).

Merlin:
I went w/ || on purpose because I want/need its NULL behavior. The
relationship between the columns with which || is used is a binary
(mutually exclusive) relationship. So they both can't be NULL nor NOT NULL.

I understand that right justification is an issue of personal taste. For me
SQL is such a verbose and dense language that I use the justification to
help break it up into visually manageable chunks. In traditional
programming languages we have curly braces and/or indentation to help us
visually organize and parse the code. I try to use justification to the
same effect. And since most code is read more frequently than it's written
I think a little realigning is a small price to pay.

I haven't investigated or encountered any doxygen processing tools. As a
matter of fact I wasn't even aware that the commenting style that I used
was called doxygen! Until recently I used to program in Java regularly
(since the Java 1.1 days) so I have a tendency to bring that style of
commenting w/ me to other languages. The version on display is a PHP'ified
variation of JavaDoc which thanks to you I just learned is called doxygen.

Like I said I'm an old Java hack and used to use IntelliJ/IDEA to sling
Java. But even though I rarely code in Java anymore I continue to use IDEA
for coding everything, except shell scripts. IDEA has support for "jump to
definition" and (more importantly) renames across files (i.e., refactoring).

Thanks again for the feedback it is truly appreciated.

Regards,

Dane
​

Re: [GENERAL] My first PL/pgSQL function

[Dane Foster]

WHEN 'coupon'
  THEN
-- This should NEVER happen!
IF discount.danglingcoupon
THEN
  DELETE FROM discount_codes WHERE code = _code;
  RAISE WARNING 'Removed dangling coupon code: %', _code;
ELSE
  IF discount.maxuse OR NOT discount.ok
  THEN
  RETURN (discount.type, 'void');
  END IF;

  IF discount.expired
  THEN
RETURN (discount.type, 'expired', discount.expd,
discount.expires);
  END IF;

  IF discount.notyet
  THEN
RETURN (
  discount.type,
  'inactive',
  discount.edate,
  discount.effective_date
);
  END IF;
  /**
   * Coupon codes can provide up to two additional parameters that
are used
   * to determine if an order meets a coupon's minimum requirements.
   *
   * int seats (i.e., cpnxtra[0]) The number of seats in the user's
order.
   * numeric subtotal (i.e., cpnxtra[1]) The order's subtotal.
   */
  IF 2 = array_length(cpnxtra, 1)
  THEN
IF discount.min IS NOT NULL
THEN
  -- @TODO - Test the regex to ensure it is escaped properly.
  IF discount.min ~ '^\$'
  THEN
IF right(discount.min, -1)::NUMERIC > cpnxtra[1]::NUMERIC
THEN
  RETURN (
discount.type,
'min',
discount.edate,
discount.effective_date,
discount.value,
discount.min
  );
END IF;
  ELSIF discount.min::INT > cpnxtra[0]::INT
  THEN
RETURN (
  discount.type,
  'min',
  discount.edate,
  discount.effective_date,
  discount.value,
  discount.min
);
  END IF;

  RETURN (
'coupon',
'ok',
discount.edate,
discount.effective_date,
discount.value,
discount.min
  );
END IF;
  END IF;

  RETURN (
'coupon',
'ok',
discount.edate,
discount.effective_date,
discount.value
  );
END IF;
  ELSE
-- This should NEVER happen!
IF discount.danglingvoucher
THEN
  DELETE FROM discount_codes WHERE code = _code;
  RAISE WARNING 'Removed dangling voucher: %', _code;
ELSE
  IF NOT discount.ok
  THEN
RETURN (discount.type, 'void', discount.mdate,
discount.modified);
  END IF;

  IF discount.expired
  THEN
RETURN (discount.type, 'expired', discount.expd,
discount.expires);
  END IF;

  IF discount.notyet
  THEN
RETURN (
  discount.type,
  'inactive',
  discount.edate,
  discount.effective_date,
  to_char(discount.value, '999D99')
);
  END IF;
  -- Please note that even though the gift certificate is valid we
return
  -- the expiration date information. This is because the data is
shown to
  -- the user to inform them of when their gift certificate expires.
  IF discount.value > 0
  THEN
RETURN (
  discount.type,
  'ok',
  discount.expd,
  discount.expires,
  to_char(discount.value, '999D99')
);
  END IF;

  RETURN (discount.type, 'depleted');
END IF;
END CASE;
  END IF;

  RETURN NULL;

END;
$$ LANGUAGE plpgsql STRICT;


Dane

On Tue, Oct 20, 2015 at 1:45 PM, Dane Foster <studdu...@gmail.com> wrote:

> On Tue, Oct 20, 2015 at 12:43 PM, Merlin Moncure <mmonc...@gmail.com>
> wrote:
>
>> On Tue, Oct 20, 2015 at 9:45 AM, Dane Foster <studdu...@gmail.com> wrote:
>> > Hello,
>> >
>> > I'm in the very very very very early stages of migrating a MySQL/PHP
>> app to
>> > PostgreSQL/PHP/Lua. Because we are moving to PostgreSQL one of the
>> [many]
>> > things I intend to change is to move ALL the SQL code/logic out of the
>> > application layer and into the database where it belongs. So after
>> months of
>> > reading the [fine] PostgreSQL manual my first experiment is to port some
>> > PHP/SQL code to a PostgreSQL function.
>> >
>> > At this stage the function is a purely academic exercise because like I
>> said
>> > before it's early days so no data has been migrated yet so I don't have
>> data
>> &

Re: [GENERAL] My first PL/pgSQL function

[Dane Foster]

On Tue, Oct 20, 2015 at 4:35 PM, John R Pierce <pie...@hogranch.com> wrote:

> On 10/20/2015 1:22 PM, Dane Foster wrote:
>
>> Here is the updated version w/ the feedback incorporated. I'm going to
>> install PostgreSQL 9.6 from source this weekend so I can start
>> testing/debugging.
>>
>
> I would stick with developing/debugging on the current release version,
> 9.4. 9.6 isn't even in beta yet.
>
> --
> 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
>
​
We are in the "let's think about how we wanna approach this" phase of
migration. So all we are doing now is researching and testing. In essence
we are playing. So by the time we get serious enough to reach beta
internally I suspect 9.6 should be in beta too. And if we happen to be
ready for production before 9.6 is ready there is always 9.5 which will
definitely be production ready before we are.

My larger point is this. I've been lurking at the edge of the PostgreSQL
community for a couple years now and I finally have a project that has
PostgreSQL written all over it. But instead of just taking the "free
software" and running w/ it I'd like to participate and one of the things
that PostgreSQL always needs is people willing to use/test the bleeding
edge of the code w/ "real" workloads. I hope to be able to contribute in
that area.

Regards,​

​Dane​

Re: [GENERAL] My first PL/pgSQL function

[Dane Foster]

On Tue, Oct 20, 2015 at 5:53 PM, Jim Nasby  wrote:

> On 10/20/15 11:43 AM, Merlin Moncure wrote:
>
>> *) let's compare notes on your doxygen style code markup. I've been
>> trouble finding a good robust tool that does exactly what I want,
>> curious if you did better.
>>
>
> Related to that (I think), you might want to move the documentation out of
> the comment block and into a COMMENT ON FUNCTION ... IS; statement. That
> way it's available through tools like pgAdmin and psql.
> --
> 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
>

​
Done! Thanks for the heads up that COMMENT ON FUNCTION ... exists.

Dane

Re: [GENERAL] My first PL/pgSQL function

[Dane Foster]

Since I'm switching to OUT parameters is there any difference
(performance/efficiency wise) between using an INTO STRICT
RECORD_TYPE_VARIABLE statement which forces me to copy/assign the property
values from the RECORD to the OUT parameter variables and simply listing
the OUT parameters, i.e., INTO STRICT outparam1, outparam2, ..., outparamN?

Thanks,

Dane

On Tue, Oct 20, 2015 at 4:37 PM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2015-10-20 22:22 GMT+02:00 Dane Foster <studdu...@gmail.com>:
>
>> Here is the updated version w/ the feedback incorporated. I'm going to
>> install PostgreSQL 9.6 from source this weekend so I can start
>> testing/debugging. Does anyone here have any experience using the pgAdmin
>> debugger recently? I ask because it seems a little dated (September 26,
>> 2008).
>>
>>
>> Thanks,
>>
>> Dane
>>
>> /**
>>  * Returns the status of a coupon or voucher.
>>  * @param _code The discount code.
>>  * @return NULL if the discount does not exist otherwise a composite type
>> (see return
>>  * type declaration below).
>>
>>  *
>>  * Voucher codes have the following properties:
>>  * type - The type of discount (voucher, giftcert).
>>  *
>>  * status   - The status of the voucher. The valid values are:
>>  *void - The voucher has been voided.
>>  *
>>  *expired  - The voucher has expired.
>>  *
>>  *inactive - The gift certificate has not been sent yet.
>>  *
>>  *ok   - The voucher has been activated, has not expired,
>> and has a
>>  *   current value greater than zero.
>>  *
>>  * date - The expiration or activation or void date of the voucher in
>> a reader
>>  *friendly format.
>>  *
>>  * datetime - The expiration or activation or void date of the gift
>> certificate in
>>  *-MM-DD HH:MM:SS format.
>>  *
>>  * value- The current value of the voucher.
>>  *
>>  * The mandatory properties are type and status. The presence of the
>> other properties
>>  * are dependent on the value of status.
>>
>>  
>> 
>>  * Coupon codes can provide the following additional parameters that are
>> used to
>>  * determine if an order meets a coupon's minimum requirements.
>>  * @param int seats The number of seats in the user's order.
>>
>>  * @param numeric subtotal The order's subtotal.
>>  *
>>  * Coupon codes have the following properties:
>>  * type - The type of discount (coupon).
>>  *
>>  * status   - The status of the coupon code. The valid values are:
>>  *void - The coupon has been voided.
>>  *
>>  *expired  - The coupon has expired.
>>  *
>>  *inactive - The coupon has not been activated yet.
>>  *
>>  *min  - The minimum seats or dollar amount requirement
>> has not been
>>  *   met.
>>  *
>>  *ok   - The coupon can be used.
>>  *
>>  * min  - The minimum seats or dollar amount requirement. The value
>> of this
>>  *property is either an unsigned integer or dollar amount
>> string w/ the
>>  *dollar sign.
>>  *
>>  * date - The expiration or activation or void date of the coupon in
>> a reader
>>  *friendly format.
>>  *
>>  * datetime - The expiration or activation or void date of the coupon in
>> -MM-DD
>>  * HH:MM:SS format.
>>  *
>>  * value- The current value of the coupon as a string. The value of
>> this property
>>  *is either an unsigned integer w/ a percent symbol or dollar
>> amount
>>  *string w/ the dollar sign.
>>  */
>> CREATE OR REPLACE FUNCTION check_discount_code(
>>   _code public.CITXT70,
>>   VARIADIC cpnxtra NUMERIC[]
>> )
>> RETURNS TABLE (
>>   type TEXT,
>>   status   TEXT,
>>   date TEXT,
>>   datetime TIMESTAMPTZ,
>>   valueTEXT,
>>   min  TEXT
>> ) AS $$
>>
>
> it is wrong, you are return composite, not SETOF composites (table).
>
> Use OUT parameters instead or declared custom type
>
> CREATE TYPE foo_result_type AS (a int, b int, c int);
> CREATE OR REPLACE FUNCTION foo(..) RETURNS foo_result_type AS $$ $$
>
>
>
>> DECLARE
>>   discount RECORD;
>> BEGIN
>>
&

[GENERAL] Returning JSON or JSONB

[Dane Foster]

Hello,

I was wondering when returning JSON data from a PostgreSQL function for
consumption by clients (e.g., PHP or Lua) does it make any difference to
declare the function's return type as JSON or JSONB?

Now that I've actually written the question down it occurs to me that what
I really want to know is which data type (JSON or JSONB or maybe TEXT) has
the lowest overhead in terms of transformation and transmission at the
PostgreSQL protocol level.

Thanks,

Dane

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

[Dane Foster]

On Fri, Oct 2, 2015 at 3:03 PM, Kevin Grittner  wrote:

> pinker  wrote:
>
> > I've tried to write audit trigger which fires only when data
> > changed, so I used "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause
> > as described in documentation. Should this clause be independent
> > from data type? because an error occurs when I'm trying to modify
> > row with point data type:
> > ERROR: could not identify an equality operator for type point
>
> > CREATE TRIGGER trigger_update_test
> > AFTER UPDATE
> > ON test1
> > FOR EACH ROW
> > WHEN ((old.* IS DISTINCT FROM new.*))
> > EXECUTE PROCEDURE test_update();
>
> Since you seem to be on 9.4, how about this?:
>
> CREATE TRIGGER trigger_update_test
> AFTER UPDATE
> ON test1
> FOR EACH ROW
> WHEN ((old *<> new))
> EXECUTE PROCEDURE test_update();
>
>
> http://www.postgresql.org/docs/9.4/static/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON
>
> Which says (in part):
>
> | To support matching of rows which include elements without a
> | default B-tree operator class, the following operators are
> | defined for composite type comparison: *=, *<>, *<, *<=, *>, and
> | *>=. These operators compare the internal binary representation
> | of the two rows. Two rows might have a different binary
> | representation even though comparisons of the two rows with the
> | equality operator is true. The ordering of rows under these
> | comparison operators is deterministic but not otherwise
> | meaningful. These operators are used internally for materialized
> | views and might be useful for other specialized purposes such as
> | replication but are not intended to be generally useful for
> | writing queries.
>
> It seems to me that auditing would be an appropriate use, because
> it would show whether there was any change in the stored value, not
> just whether the old and new values were equal in a btree ordering
> comparison.  For example, if a citext column were changed from 'a'
> to 'A', it would compare as equal with its type's "=" operator, but
> the row would show as changed anyway, if you use "*=" or "*<>".
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
​
Hi,

Would you please provide the link to the section in the documentation that
you are referring to because I'm new to PostgreSQL and I didn't know WHEN
could be used outside of CASE and EXCEPTION blocks.

Thanks.

Dane
​

Re: [GENERAL] Q: text query search and

[Dane Foster]

Kevin,

I am also interested in a deeper explanation of what you mean by, The easy
way to do that is just to add a trigram index and search for similar
strings, and forget about full text search. Because I need to make a
decision about whether to use full text search or use other pattern
matching facilities such as LIKE and/or regular expressions. For me, the
reason I don't just default to full text search is the documents are
relative small (i.e, HTML = 128K) and number fewer than 10,000 so I'm not
sure if the effort expended to learn the ins/outs of full text search will
be beneficial to my use case.

Regards,

Dane

On Thu, Jul 23, 2015 at 12:17 PM, Jeff Janes jeff.ja...@gmail.com wrote:

 On Thu, Jul 23, 2015 at 2:55 AM, amihay gonen agone...@gmail.com wrote:

 Hi  I'm trying to implement a text search in PG .

 My goal to enable the user search on several columns also on partial
 words.

 here is sample code :
 create table test_test( text_data tsvector, text_a varchar,text_b
 varchar);

 insert into test_test(text_a,text_b) select 'name 10.10.2.3 ','name3
 name' from generate_series(1,500);
 update test_test set text_data=to_tsvector(text_a||' '||text_b);
 CREATE INDEX test_test_idx ON test_test USING gin(text_data);

 explain ANALYZE select * from test_test where text_data@
 @plainto_tsquery('name');

 my questions are :
 1. why the index is not used (I guess it is related to the way the data
 is generated)


 It returns the entire table, so there is no point in using an index.  Yes,
 it is the way it is generated, the same data repeated over and over is not
 very realistic.  If you just want random text, I use md5(random()::text).
 But if you want text that looks vaguely like English, I don't have a nice
 trick for that.  Maybe load the sgml files into a table.


 2, how can I use pg_trgm with ts_vector to enable to answer query like
 10.10 or nam ?
the idea is to use the gin index , maybe there are other option
 without using pg_trgm?


 Do you mean:

 WHERE text_a LIKE '%10.10%' or text_a LIKE '%nam%' ?

 With the or, that going to be hard to optimize.

 Anyway, pg_tgrm requires its own special GIN index, it can't piggy back on
 the tsvector GIN index.

 CREATE INDEX whatever ON test_test USING gin(text_a gin_trgm_ops);

 or

 CREATE INDEX whatever ON test_test USING gin((text_a||' '||text_b)
 gin_trgm_ops);

 But, LIKE '%10.10%' is going to be tough for a pg_trgm index to help with,
 unless you compile your own code after removing #define KEEPONLYALNUM

 Cheers,

 Jeff

[GENERAL] A table of magic constants

[Dane Foster]

Hello,

My reason/excuse for asking what I'll eventually ask is that I'm a new
PostgreSQL practitioner so the amount of things I don't know about
PostgreSQL is depressingly large.

While reading through the recent Row level security - notes and questions
thread I saw this SQL statement:

CREATE POLICY accounts_policy ON testrls.accounts
FOR ALL
TO users
USING (true)
WITH CHECK (username = SESSION_USER);

The bit that is the catalyst for this e-mail is: SESSION_USER.

As a recent convert to the Church of Postgres I've been consuming vast
amounts of information on PostgreSQL, and SESSION_USER is not the first nor
only, what I'm calling magic constant, that I've seen. Off the top of my
head, other examples that I've encountered are CURRENT_USER and
CURRENT_TIMESTAMP.

So my question is this, is there a reference table in the documentation
that I haven't found yet that lists all magic constants and their meaning?
And if not in the official documentation is it in the wiki?

Thanks,

Dane

Re: [GENERAL] A table of magic constants

[Dane Foster]

Hello Adrian,

Thank you for the additional reference links but my concern was less about
how to find out what a function (formerly magic constant) that I
encountered in the wild did but more about having a list that would educate
newcomers/me about what is automatically available for use. For example, in
the RLS example from my original message, had I the same or similar need as
the poster I would not have been able to formulate the policy that I quoted
because I had no clue that SESSION_USER even existed. Specifically I would
not have been able to formulate the following clause, ... WITH CHECK
(username = SESSION_USER), w/o first knowing that SESSION_USER was a thing.

​Regards,​

Dane

On Sat, Jul 11, 2015 at 3:54 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:

 On 07/11/2015 12:21 PM, Raymond O'Donnell wrote:

 On 11/07/2015 20:07, Dane Foster wrote:

 snip

  As a recent convert to the Church of Postgres I've been consuming vast


 Welcome to the One True Faith! :-)

  amounts of information on PostgreSQL, and SESSION_USER is not the first
 nor only, what I'm calling magic constant, that I've seen. Off the top
 of my head, other examples that I've encountered are CURRENT_USER and
 CURRENT_TIMESTAMP.

 So my question is this, is there a reference table in the documentation
 that I haven't found yet that lists all magic constants and their
 meaning? And if not in the official documentation is it in the wiki?


 session_user, current_timestamp and current_user are all functions, not
 magic constants:

http://www.postgresql.org/docs/9.4/static/functions-datetime.html
http://www.postgresql.org/docs/9.4/static/functions-info.html

 I hope this helps,


 To add to this. In the documentation section:

 http://www.postgresql.org/docs/9.4/interactive/index.html

 there is a Search box where you can enter the word/phrase you are looking
 for.

 If all else fails there is the Index:

 http://www.postgresql.org/docs/9.4/interactive/index.html



 Ray.



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

Re: [GENERAL] A table of magic constants

[Dane Foster]

Hi Michael,

You nailed it. I am reading the documentation cover to cover. I started
chapter 9 two weeks ago but haven't found the time to read beyond 9.1 yet.
But for day to day usage on the MySQL to PostgreSQL migration project that
I'm working on I jump around in the docs as needed to find specific things
that are further ahead in the docs than chapter 9.

Dane

On Sat, Jul 11, 2015 at 6:34 PM, Michael Nolan htf...@gmail.com wrote:



 On Sat, Jul 11, 2015 at 4:53 PM, Vincent Veyron vv.li...@wanadoo.fr
 wrote:

 On Sat, 11 Jul 2015 16:55:44 -0400
 Dane Foster studdu...@gmail.com wrote:




 . After a while, you'll find your way around the documentation.

 I've been doing it almost every day for years, still learning every time.


 I highly recommend reading the documentation from 'cover to cover'
 periodically.  Yes, there will be things you don't understand yet, but each
 time you'll pick up things you didn't get in previous passes.  A lot of
 people have put in a lot of time on that documentation, and it is
 first-rate. (I've been working on a project that requires MySQL, their
 documentation is far inferior.)
 --
 Mike Nolan
 no...@tssi.com

[GENERAL] Dynamic multi dimensional arrays in SQL

[Dane Foster]

Hello,

I'm trying to dynamically construct a multi dimensional array where the
outer most array's elements are binary arrays. My initial attempt was
something to the effect of:
SELECT ARRAY(SELECT ARRAY[true::text,false::text] FROM
generate_series(1,2));

My expectation is a result of the form: {{true,false},{true,false}}

But it doesn't work. I get the following error instead: could not find
array type for data type text[].

The only way I've gotten it to work is to concatenate strings and
explicitly cast the string to an array. It's ugly. I'm hoping the issue is
that I'm doing something wrong and not that string concatenation or a
procedural language are the only solutions.

Any insight into what I'm doing wrong would be appreciated.

Regards,
​

Dane

[GENERAL] Why does the range type's upper function behave inconsistently?

[Dane Foster]

I don't understand the inconsistent behavior of the range types' upper
function in regard to inclusive ranges.

For example(s):
1. SELECT upper(int4range(1, 4, '[]')) = 4; -- FALSE
2. SELECT upper(int8range(1, 4, '[]')) = 4; -- FALSE
3. SELECT upper(numrange(1, 4, '[]')) = 4; -- TRUE
4. SELECT upper(tstzrange('2015-07-01: 00:00:00', now(), '[]')) = now(); --
TRUE
5. SELECT upper(daterange('2015-01-01', current_date, '[]')) =
current_date; -- FALSE

#1  #2 are FALSE because upper returns 5 instead of 4; and #5 is FALSE
because upper returns: current_date + interval '1 day'. I don't understand
the logic behind why it would return the inclusive upper bound value for
some ranges and not others. If anyone can shed some light on this behavior
it would be greatly appreciated.

One of things I originally tried to use upper for was CHECK constraints.
That was until I wrote some unit tests and realized that upper doesn't
consistently work the way I expected. Of course my assumptions are probably
wrong so that's why I'm asking for clarification.

Regards,

Dane

Re: [GENERAL] Why does the range type's upper function behave inconsistently?

[Dane Foster]

Thanks everyone. I understand now. The funny thing is I read the
documentation many weeks before actually using range types for the first
time but it didn't click that the documentation was describing the behavior
I was observing, until now.

Thanks again,


Dane

On Sun, Jul 5, 2015 at 1:33 PM, Adrian Klaver adrian.kla...@aklaver.com
wrote:

 On 07/05/2015 10:13 AM, Dane Foster wrote:

 I don't understand the inconsistent behavior of the range types' upper
 function in regard to inclusive ranges.

 For example(s):
 1. SELECT upper(int4range(1, 4, '[]')) = 4; -- FALSE
 2. SELECT upper(int8range(1, 4, '[]')) = 4; -- FALSE
 3. SELECT upper(numrange(1, 4, '[]')) = 4; -- TRUE
 4. SELECT upper(tstzrange('2015-07-01: 00:00:00', now(), '[]')) = now();
 -- TRUE
 5. SELECT upper(daterange('2015-01-01', current_date, '[]')) =
 current_date; -- FALSE


 To follow up on Julien Rouhaud post, if you do:

 production=# select daterange('2015-01-01', current_date, '[]');
 daterange
 -
  [2015-01-01,2015-07-06)
 (1 row)

 see that the '[]] has been changed to '[)' with tomorrows date as the
 upper bound.



 #1  #2 are FALSE because upper returns 5 instead of 4; and #5 is FALSE
 because upper returns: current_date + interval '1 day'. I don't
 understand the logic behind why it would return the inclusive upper
 bound value for some ranges and not others. If anyone can shed some
 light on this behavior it would be greatly appreciated.

 One of things I originally tried to use upper for was CHECK constraints.
 That was until I wrote some unit tests and realized that upper doesn't
 consistently work the way I expected. Of course my assumptions are
 probably wrong so that's why I'm asking for clarification.

 Regards,

 Dane



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