Postgres Pro
Downloads
Home   >   mailing lists

Thread: Getting non_NULL right-side values on a non-matching join?

Getting non_NULL right-side values on a non-matching join?

From
Ken Tanzer
Date:
Hello.  In doing a left join with a particular view as the right table, and non-matching join criteria, I am getting values returned in a few fields.  All the rest are NULL.  I would expect all the right side values to be NULL.

(The view is large and messy, but it doesn't seem like that should matter.  I've attached the create statement for the view anyway.)

Am I missing something really really obvious about LEFT JOINs here?  This statement seems to confirm my expectations:  

"This query is called a left outer join because the table mentioned on the left of the join operator will have each of its rows in the output at least once, whereas the table on the right will only have those rows output that match some row of the left table. When outputting a left-table row for which there is no right-table match, empty (null) values are substituted for the right-table columns."

Thanks in advance.

Ken

-- Nothing special about the -1 id here, just an example of a non-matching value.

ag_spc=> SELECT foo.client_id AS foo_id,rent_info.client_id AS ri_id,move_in_type,annual_income,monthly_income_total,dependent_count from (SELECT -1 AS client_id) foo LEFT JOIN rent_info USING (client_id);
 foo_id | ri_id | move_in_type | annual_income | monthly_income_total | dependent_count 
--------+-------+--------------+---------------+----------------------+-----------------
     -1 |       | Move-in      |             0 |                    0 |               0
(1 row)


ag_spc=> SELECT * FROM rent_info WHERE client_id = -1;
 effective_date | effective_date_end | rent_amount_tenant_calculated | rent_amount_tenant | rent_amount_spc | project_date | own_date | is_active_manual | residence_own_id | client_id | housing_project_code | housing
_project_label | housing_unit_code | residence_date | residence_date_end | was_received_hap | was_received_compliance | moved_from_code | chronic_homeless_status_code | move_in_type | lease_on_file | moved_to_code | 
moved_to_unit | departure_type_code | departure_reason_code | move_out_was_code | returned_homeless | unit_rent_manual | tenant_pays_deposit | comment_damage | comment_deposit | comment | income_id | income_date | in
come_date_end | annual_income | monthly_income_total | monthly_income_primary | income_primary_code | monthly_income_secondary | income_secondary_code | monthly_income_tertiary | income_tertiary_code | monthly_intere
st_income | other_assistance_codes | income_certification_type_code | child_care | handicap_assistance | medical_expense | fund_type_code | rent_date_effective | rent_date_end | housing_unit_id | housing_unit_label |
 housing_unit_date | housing_unit_date_end | unit_type_code | tax_credit | max_occupant | alternate_address_id | mailing_address_unit | street_address | mailing_address_client | housing_unit_subsidy_id | housing_unit
_subsidy_date | housing_unit_subsidy_date_end | unit_subsidy_amount | unit_rent | tenant_vendor_number | vendor_number | utility_allowance_manual | utility_allowance_unit | utility_allowance | utility_allowance_code 
| security_deposit | fair_market_rent | rent_amount_tenant_manual | dependent_count | cid | reg_spc_date | reg_spc_date_end | grant_number_code | agency_code | agency_phone | agency_label | agency_contact | fake_key 
----------------+--------------------+-------------------------------+--------------------+-----------------+--------------+----------+------------------+------------------+-----------+----------------------+--------
---------------+-------------------+----------------+--------------------+------------------+-------------------------+-----------------+------------------------------+--------------+---------------+---------------+-
--------------+---------------------+-----------------------+-------------------+-------------------+------------------+---------------------+----------------+-----------------+---------+-----------+-------------+---
--------------+---------------+----------------------+------------------------+---------------------+--------------------------+-----------------------+-------------------------+----------------------+---------------
----------+------------------------+--------------------------------+------------+---------------------+-----------------+----------------+---------------------+---------------+-----------------+--------------------+
-------------------+-----------------------+----------------+------------+--------------+----------------------+----------------------+----------------+------------------------+-------------------------+-------------
--------------+-------------------------------+---------------------+-----------+----------------------+---------------+--------------------------+------------------------+-------------------+------------------------
+------------------+------------------+---------------------------+-----------------+-----+--------------+------------------+-------------------+-------------+--------------+--------------+----------------+----------
(0 rows)






--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.
Attachment

Re: Getting non_NULL right-side values on a non-matching join?

From
Kevin Grittner
Date:
Ken Tanzer <ken.tanzer@gmail.com> wrote:

> In doing a left join with a particular view as the right table,
> and non-matching join criteria, I am getting values returned in a
> few fields.  All the rest are NULL.  I would expect all the right
> side values to be NULL.

What is the output of executing?:

SELECT version();

There used to be some bugs in this area, but they should be fixed
in the current releases, AFAIK.

http://www.postgresql.org/support/versioning/

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

Re: Getting non_NULL right-side values on a non-matching join?

From
Ken Tanzer
Date:
On Thu, Nov 21, 2013 at 2:22 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
Ken Tanzer <ken.tanzer@gmail.com> wrote:

> In doing a left join with a particular view as the right table,
> and non-matching join criteria, I am getting values returned in a
> few fields.  All the rest are NULL.  I would expect all the right
> side values to be NULL.

What is the output of executing?:

SELECT version();

I think I'm current on 9.2.5 / CentOs 6.4.

ag_spc=> SELECT version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
(1 row)

[spc@hosting agency_code]$ rpm -qi postgresql92
Name        : postgresql92                 Relocations: (not relocatable)
Version     : 9.2.5                             Vendor: (none)
Release     : 1PGDG.rhel6                   Build Date: Wed 09 Oct 2013 06:02:22 AM PDT
Install Date: Tue 15 Oct 2013 06:22:39 PM PDT      Build Host: koji-sl6-x86-64-pg92
Group       : Applications/Databases        Source RPM: postgresql92-9.2.5-1PGDG.rhel6.src.rpm
Size        : 5279557                          License: PostgreSQL
Signature   : DSA/SHA1, Wed 09 Oct 2013 06:02:31 AM PDT, Key ID 1f16d2e1442df0f8
URL         : http://www.postgresql.org/
Summary     : PostgreSQL client programs and libraries
...


Don't know if this is useful information, but I was surprised that the problem continues even wrapping the view as a subquery, and then even if the subquery has a client_id IS NOT NULL clause:

ag_spc=> SELECT foo.client_id AS foo_id,boo.client_id AS ri_id,move_in_type,annual_income,monthly_income_total,dependent_count FROM (SELECT -1 AS client_id) foo LEFT OUTER JOIN (SELECT * FROM rent_info WHERE client_id IS NOT NULL) boo USING (client_id);
 foo_id | ri_id | move_in_type | annual_income | monthly_income_total | dependent_count 
--------+-------+--------------+---------------+----------------------+-----------------
     -1 |       | Move-in      |             0 |                    0 |               0
(1 row)

I'm happy to provide whatever additional information is helpful--just let me know.  Thanks.

Ken



--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Getting non_NULL right-side values on a non-matching join?

From
Tom Lane
Date:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hello.  In doing a left join with a particular view as the right table, and
> non-matching join criteria, I am getting values returned in a few fields.
>  All the rest are NULL.  I would expect all the right side values to be
> NULL.

Hmmm ... the join conditions involving COALESCE() remind me of a bug I
just fixed last week.  Are you in a position to try a patch?  If so,
here's the fix against 9.2:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=51b6ae6bba75bca2374a24cf7c740da74c955ad5

If that doesn't help, please see if you can extract a self-contained
test case.

            regards, tom lane

Re: Getting non_NULL right-side values on a non-matching join?

From
Ken Tanzer
Date:
On Thu, Nov 21, 2013 at 5:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hello.  In doing a left join with a particular view as the right table, and
> non-matching join criteria, I am getting values returned in a few fields.
>  All the rest are NULL.  I would expect all the right side values to be
> NULL.

Hmmm ... the join conditions involving COALESCE() remind me of a bug I
just fixed last week.  Are you in a position to try a patch?  If so,
here's the fix against 9.2:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=51b6ae6bba75bca2374a24cf7c740da74c955ad5

If that doesn't help, please see if you can extract a self-contained
test case.

Getting a build environment together seemed more painful, so here's a test case.  Just for fun, I tried this in another database on a different machine (and with 9.0.08).  I got the same results, so it doesn't seem to be a case of something wacky with my particular database.

Cheers,
Ken

p.s.,  Not your problem I know, but I need to deal with this somehow and rather soon.  If the patch you mentioned does fix this, and that's the easiest way to get this fixed on my machine, please do let me know and I'll start googling Postgres build source.  Thanks!


DROP VIEW IF EXISTS boo_top_view;
DROP VIEW IF EXISTS boo_view;
DROP TABLE IF EXISTS boo_table;
DROP TABLE IF EXISTS a_table;

CREATE TABLE boo_table ( client_id INTEGER);
CREATE TABLE a_table ( client_id INTEGER );

CREATE OR REPLACE VIEW boo_view AS
SELECT
    r1.client_id,
    666 AS my_field
FROM boo_table r1;

CREATE OR REPLACE VIEW boo_top_view AS
SELECT 
    client_id,
    my_field
FROM (
    SELECT
        a.client_id,
        a.my_field
    FROM boo_view AS a
) foo
--Problem goes away if you take out this left join
LEFT JOIN (
    SELECT client_id FROM  a_table
)  a2 USING (client_id);

SELECT foo.client_id AS foo_id,boo.client_id AS ri_id,my_field from (SELECT -1 AS client_id) foo LEFT JOIN boo_top_view boo USING (client_id);

 foo_id | ri_id | my_field 
--------+-------+----------
     -1 |       |      666




--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Getting non_NULL right-side values on a non-matching join?

From
Ken Tanzer
Date:
The issue also seems tied to the non-NULL constant in the view.  

This one yields rows
  33::int AS b_field

This one doesn't
    NULL::int AS b_field

DROP VIEW IF EXISTS boo_top_view;
DROP VIEW IF EXISTS boo_view;
DROP TABLE IF EXISTS boo_table;
DROP TABLE IF EXISTS a_table;

CREATE TABLE boo_table ( client_id INTEGER,b_field INTEGER);
CREATE TABLE a_table ( client_id INTEGER );

CREATE OR REPLACE VIEW boo_view AS
SELECT
    r1.client_id,
--  This one yields rows
    33::int AS b_field
--  This one doesn't
--  NULL::int AS b_field

FROM boo_table r1;

CREATE OR REPLACE VIEW boo_top_view AS
SELECT 
    client_id,
--    my_field
    b_field
FROM (
    SELECT
        a.client_id,
--      a.my_field
a.b_field
    FROM boo_view AS a
) foo
--Problem goes away if you take out this left join
LEFT JOIN (
    SELECT client_id FROM  a_table
)  a2 USING (client_id);

SELECT foo.client_id AS foo_id,boo.client_id AS ri_id,b_field from (SELECT -1 AS client_id) foo LEFT JOIN boo_top_view boo USING (client_id);





On Thu, Nov 21, 2013 at 7:49 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
On Thu, Nov 21, 2013 at 5:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hello.  In doing a left join with a particular view as the right table, and
> non-matching join criteria, I am getting values returned in a few fields.
>  All the rest are NULL.  I would expect all the right side values to be
> NULL.

Hmmm ... the join conditions involving COALESCE() remind me of a bug I
just fixed last week.  Are you in a position to try a patch?  If so,
here's the fix against 9.2:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=51b6ae6bba75bca2374a24cf7c740da74c955ad5

If that doesn't help, please see if you can extract a self-contained
test case.

Getting a build environment together seemed more painful, so here's a test case.  Just for fun, I tried this in another database on a different machine (and with 9.0.08).  I got the same results, so it doesn't seem to be a case of something wacky with my particular database.

Cheers,
Ken

p.s.,  Not your problem I know, but I need to deal with this somehow and rather soon.  If the patch you mentioned does fix this, and that's the easiest way to get this fixed on my machine, please do let me know and I'll start googling Postgres build source.  Thanks!


DROP VIEW IF EXISTS boo_top_view;
DROP VIEW IF EXISTS boo_view;
DROP TABLE IF EXISTS boo_table;
DROP TABLE IF EXISTS a_table;

CREATE TABLE boo_table ( client_id INTEGER);
CREATE TABLE a_table ( client_id INTEGER );

CREATE OR REPLACE VIEW boo_view AS
SELECT
    r1.client_id,
    666 AS my_field
FROM boo_table r1;

CREATE OR REPLACE VIEW boo_top_view AS
SELECT 
    client_id,
    my_field
FROM (
    SELECT
        a.client_id,
        a.my_field
    FROM boo_view AS a
) foo
--Problem goes away if you take out this left join
LEFT JOIN (
    SELECT client_id FROM  a_table
)  a2 USING (client_id);

SELECT foo.client_id AS foo_id,boo.client_id AS ri_id,my_field from (SELECT -1 AS client_id) foo LEFT JOIN boo_top_view boo USING (client_id);

 foo_id | ri_id | my_field 
--------+-------+----------
     -1 |       |      666




--
AGENCY Software  
A data system that puts you in control
100% Free Software

learn more about AGENCY or
follow the discussion.



--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Getting non_NULL right-side values on a non-matching join?

From
Tom Lane
Date:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> Getting a build environment together seemed more painful, so here's a test
> case.

Wow, that's pretty broken.  I'll dig into it tomorrow.

> Just for fun, I tried this in another database on a different
> machine (and with 9.0.08).  I got the same results, so it doesn't seem to
> be a case of something wacky with my particular database.

Yeah, seems to fail the same way in all active branches ...

> p.s.,  Not your problem I know, but I need to deal with this somehow and
> rather soon.

If you just need a work-around-it-right-now solution, I'd suggest
introducing an "OFFSET 0" optimization fence into one or another of the
levels of view below the outer joins.  I've not experimented but I think
that ought to fix it, at some possibly-annoying cost in query
optimization.  Hopefully I'll have another answer tomorrow.

            regards, tom lane

Re: Getting non_NULL right-side values on a non-matching join?

From
Tom Lane
Date:
I wrote:
> If you just need a work-around-it-right-now solution, I'd suggest
> introducing an "OFFSET 0" optimization fence into one or another of the
> levels of view below the outer joins.  I've not experimented but I think
> that ought to fix it, at some possibly-annoying cost in query
> optimization.  Hopefully I'll have another answer tomorrow.

I found a less nasty workaround: if you replace "my_field" by
"foo.my_field" in the SELECT list of boo_top_view, the problem goes away.
The bug seems to be due to wrong processing of join alias variables
during subquery pullup.  The unqualified name "my_field" is a reference
to an output alias of the unnamed LEFT JOIN in that view, but if you
qualify it with the name of the component table, it's not an alias
anymore so the bug doesn't trigger.

Thanks for reporting this!  I'll try to make sure there's a real fix
in the next update releases, which will be out PDQ because of the
replication bug that was identified this week.

            regards, tom lane

Re: Getting non_NULL right-side values on a non-matching join?

From
Ken Tanzer
Date:
On Fri, Nov 22, 2013 at 8:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:
> If you just need a work-around-it-right-now solution, I'd suggest
> introducing an "OFFSET 0" optimization fence into one or another of the
> levels of view below the outer joins.  I've not experimented but I think
> that ought to fix it, at some possibly-annoying cost in query
> optimization.  Hopefully I'll have another answer tomorrow.

I found a less nasty workaround: if you replace "my_field" by
"foo.my_field" in the SELECT list of boo_top_view, the problem goes away.
The bug seems to be due to wrong processing of join alias variables
during subquery pullup.  The unqualified name "my_field" is a reference
to an output alias of the unnamed LEFT JOIN in that view, but if you
qualify it with the name of the component table, it's not an alias
anymore so the bug doesn't trigger.

Thanks for reporting this!  I'll try to make sure there's a real fix
in the next update releases, which will be out PDQ because of the
replication bug that was identified this week.

                        regards, tom lane

I'm happy to be of assistance, but mostly glad it wasn't a me-bug! :)  I put an alias on every field reference in the view, and the problem did indeed go away.  Thank you very much for providing an easy workaround!

Cheers,
Ken

--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Getting non_NULL right-side values on a non-matching join?

From
Tom Lane
Date:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Fri, Nov 22, 2013 at 8:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I found a less nasty workaround: if you replace "my_field" by
>> "foo.my_field" in the SELECT list of boo_top_view, the problem goes away.

> I'm happy to be of assistance, but mostly glad it wasn't a me-bug! :)  I
> put an alias on every field reference in the view, and the problem did
> indeed go away.  Thank you very much for providing an easy workaround!

Great, I'm glad that was good enough for you.  There's a real fix
committed here:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=c0aa210f6ebab06ca3933c735c7c6d2b8bdd024e
but since you expressed some discomfort about patching source before,
it's probably best if you just work around it till we put out new
releases.

            regards, tom lane

Re: Getting non_NULL right-side values on a non-matching join?

From
Ken Tanzer
Date:
Thanks Tom.  The workaround seems harmless, even good coding practice, so it's all good on my end.  If it were useful to you I'd gladly build and test it, but I doubt that's the case.  But just say the word!  Otherwise it's just a question of time and priorities, and it seems likely to chew up at least a few hours if I'm careful about it.

OTOH, if there were a very clear and credible page with good instructions on installing build environment + postgres (for say RHEL, Ubuntu & Fedora) that would install side by side with an existing installation (and how to remove it all cleanly) it would make me and maybe others more able/likely to test patches.  There may be such a page--I just didn't find it.  And I was somewhat dissuaded from building an RPM on my CentOs machine by the note in the Postgres wiki that the ubuntu packages allow "multiple versions more easily than other packaging schemes."

Just a thought.  I know all the information is out there and can be pieced together.  Like many computing endeavors, I'm sure the second time would be quick and easy, but likely not so much the first!

Cheers,
Ken


On Fri, Nov 22, 2013 at 8:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Fri, Nov 22, 2013 at 8:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I found a less nasty workaround: if you replace "my_field" by
>> "foo.my_field" in the SELECT list of boo_top_view, the problem goes away.

> I'm happy to be of assistance, but mostly glad it wasn't a me-bug! :)  I
> put an alias on every field reference in the view, and the problem did
> indeed go away.  Thank you very much for providing an easy workaround!

Great, I'm glad that was good enough for you.  There's a real fix
committed here:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=c0aa210f6ebab06ca3933c735c7c6d2b8bdd024e
but since you expressed some discomfort about patching source before,
it's probably best if you just work around it till we put out new
releases.

                        regards, tom lane



--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Getting non_NULL right-side values on a non-matching join?

From
Vik Fearing
Date:
On 11/23/2013 07:41 AM, Ken Tanzer wrote:
OTOH, if there were a very clear and credible page with good instructions on installing build environment + postgres (for say RHEL, Ubuntu & Fedora) that would install side by side with an existing installation (and how to remove it all cleanly) it would make me and maybe others more able/likely to test patches.  There may be such a page--I just didn't find it.  And I was somewhat dissuaded from building an RPM on my CentOs machine by the note in the Postgres wiki that the ubuntu packages allow "multiple versions more easily than other packaging schemes."

Just a thought.  I know all the information is out there and can be pieced together.  Like many computing endeavors, I'm sure the second time would be quick and easy, but likely not so much the first!

Chapter 15 of our documentation handles installing from source.
http://www.postgresql.org/docs/current/static/installation.html
-- 
Vik

Re: Getting non_NULL right-side values on a non-matching join?

From
Ken Tanzer
Date:



On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing <vik.fearing@dalibo.com> wrote:
On 11/23/2013 07:41 AM, Ken Tanzer wrote:
OTOH, if there were a very clear and credible page with good instructions on installing build environment + postgres (for say RHEL, Ubuntu & Fedora) that would install side by side with an existing installation (and how to remove it all cleanly) it would make me and maybe others more able/likely to test patches.  There may be such a page--I just didn't find it.  And I was somewhat dissuaded from building an RPM on my CentOs machine by the note in the Postgres wiki that the ubuntu packages allow "multiple versions more easily than other packaging schemes."

Just a thought.  I know all the information is out there and can be pieced together.  Like many computing endeavors, I'm sure the second time would be quick and easy, but likely not so much the first!

Chapter 15 of our documentation handles installing from source.
http://www.postgresql.org/docs/current/static/installation.html
-- 
Vik
Thanks for the link.  I really do appreciate all the documentation that Postgres has put together.  In this case I especially like the short version provided, which covers part of what I was looking for.  It would be great if there were a similar page that addressed how to set this up side-by-side with an existing installation, and had a cheat sheet for pulling in build tools and libraries.  (As in, on Cent OS run "yum install x y z...", Ubunutu "apt-get install a x z".)  I get that the build environment and libraries are outside of the scope of Postgres proper and maybe unfair to ask it be documented, but they're still steps people have to go through.  If they were included in that short version format, it would be fantastic!

Cheers,
Ken




--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Getting non_NULL right-side values on a non-matching join?

From
Adrian Klaver
Date:
On 11/23/2013 02:45 AM, Ken Tanzer wrote:
>
>
>
> On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing <vik.fearing@dalibo.com
> <mailto:vik.fearing@dalibo.com>> wrote:
>
>     On 11/23/2013 07:41 AM, Ken Tanzer wrote:
>>     OTOH, if there were a very clear and credible page with good
>>     instructions on installing build environment + postgres (for say
>>     RHEL, Ubuntu & Fedora) that would install side by side with an
>>     existing installation (and how to remove it all cleanly) it would
>>     make me and maybe others more able/likely to test patches.  There
>>     may be such a page--I just didn't find it.  And I was somewhat
>>     dissuaded from building an RPM on my CentOs machine by the note in
>>     the Postgres wiki that the ubuntu packages allow "multiple
>>     versions more easily than other packaging schemes."
>>
>>     Just a thought.  I know all the information is out there and can
>>     be pieced together.  Like many computing endeavors, I'm sure the
>>     second time would be quick and easy, but likely not so much the first!
>
>     Chapter 15 of our documentation handles installing from source.
>     http://www.postgresql.org/docs/current/static/installation.html
>
>     --
>     Vik
>
> Thanks for the link.  I really do appreciate all the documentation that
> Postgres has put together.  In this case I especially like the short
> version provided, which covers part of what I was looking for.  It would
> be great if there were a similar page that addressed how to set this up
> side-by-side with an existing installation, and had a cheat sheet for
> pulling in build tools and libraries.  (As in, on Cent OS run "yum
> install x y z...", Ubunutu "apt-get install a x z".)  I get that the
> build environment and libraries are outside of the scope of Postgres
> proper and maybe unfair to ask it be documented, but they're still steps
> people have to go through.  If they were included in that short version
> format, it would be fantastic!


You will need the basic build tools. In Debian/Ubuntu that is
build-essential in RH/CentOS that is 'Development Tools'. The other
devel libraries will depend on what you want to include in the build. So
for example if you want to use OpenSSL you will need libopenssl-devel
and if you want plpythonu you will need python-devel. Running
./configure will help you in that regard, it will flag those libraries
not present. As to a separate installation, that is something you set up
in the configure step. The two important things to know is that the new
instance needs to be in a separate directory from the old and it needs
to listen on a different port. You can install the new instance in your
own home directory if that suits. Both the steps can be handled as follows:

$ ./configure --with-python --with-openssl
--prefix=/home/aklaver/pgsqlTest --with-pgport=5462


When you run it, use the appropriate binaries. In the example I show
above they would be in /home/aklaver/pgsqlTest/bin/. If you want you
could set up symlinks to the binaries to make it easier, that is what
the Debian/Ubuntu process does(among other things).

>
> Cheers,
> Ken
>
>
>
>
>
> follow the discussion.


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Getting non_NULL right-side values on a non-matching join?

From
Tom Lane
Date:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing <vik.fearing@dalibo.com> wrote:
>> Chapter 15 of our documentation handles installing from source.
>> http://www.postgresql.org/docs/current/static/installation.html

> Thanks for the link.  I really do appreciate all the documentation that
> Postgres has put together.  In this case I especially like the short
> version provided, which covers part of what I was looking for.  It would be
> great if there were a similar page that addressed how to set this up
> side-by-side with an existing installation, and had a cheat sheet for
> pulling in build tools and libraries.  (As in, on Cent OS run "yum install
> x y z...", Ubunutu "apt-get install a x z".)  I get that the build
> environment and libraries are outside of the scope of Postgres proper and
> maybe unfair to ask it be documented, but they're still steps people have
> to go through.  If they were included in that short version format, it
> would be fantastic!

FWIW, I think this is outside the scope of Chapter 15, and especially
outside the scope of the short version ;-).  If you're not wanting to
do the /usr/local approach, you're most likely wanting to build a
replacement for some distro-supplied packaging of Postgres.  There
are too many of those, and they change too often, for us to be able
to provide reasonable instructions for that in our formal docs.
Moreover, 99% of what you need to know for that is not PG-specific but
distro-specific.

Perhaps it'd be worth setting up page(s) on our wiki about this, though?
The question certainly comes up often enough.

            regards, tom lane

Re: Getting non_NULL right-side values on a non-matching join?

From
Ken Tanzer
Date:



On Sat, Nov 23, 2013 at 9:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Sat, Nov 23, 2013 at 2:20 AM, Vik Fearing <vik.fearing@dalibo.com> wrote:
>> Chapter 15 of our documentation handles installing from source.
>> http://www.postgresql.org/docs/current/static/installation.html

> Thanks for the link.  I really do appreciate all the documentation that
> Postgres has put together.  In this case I especially like the short
> version provided, which covers part of what I was looking for.  It would be
> great if there were a similar page that addressed how to set this up
> side-by-side with an existing installation, and had a cheat sheet for
> pulling in build tools and libraries.  (As in, on Cent OS run "yum install
> x y z...", Ubunutu "apt-get install a x z".)  I get that the build
> environment and libraries are outside of the scope of Postgres proper and
> maybe unfair to ask it be documented, but they're still steps people have
> to go through.  If they were included in that short version format, it
> would be fantastic!

FWIW, I think this is outside the scope of Chapter 15, and especially
outside the scope of the short version ;-).  If you're not wanting to
do the /usr/local approach, you're most likely wanting to build a
replacement for some distro-supplied packaging of Postgres.  

Well yes and no.  In my case I'm actually running CentOs with the PGDG packages straight from you folks.  

My starting point was that right now I don't have a non-production spare machine.  So I need to _assure_ myself I'm not going to screw things up (time sink!).  I don't keep a build environment, but don't mind installing one temporarily.

The two approaches I'd thought of were:

1) Build a new binary, stop server, move new binary into place; start server
(Not sure if this works or not!)  

2) Build the whole thing, install and run side-by-side

So maybe /usr/local would work, but it seems like SRPM + patch will be the closest to matching my current setup, and do the quickest job of pulling in everything needed.

 
There
are too many of those, and they change too often, for us to be able
to provide reasonable instructions for that in our formal docs.
Moreover, 99% of what you need to know for that is not PG-specific but
distro-specific.

Perhaps it'd be worth setting up page(s) on our wiki about this, though?
The question certainly comes up often enough.

                        regards, tom lane

As a side thought, what about creating a source RPM configured to install side-by-side, and on a different port?  Wouldn't that be just a few tweaks to make?  And since you're maintaining packages anyway...

But if not, it would be great if the wiki page included a "to build a side-by-side RPM, these are the X things you have to change after you install the source" section, and I guess that would work for the distro-supplied RPMs as well.

Then I could boil it down to:

rpm -qa > original.packages
Download source RPM
yum-builddep source RPM
rpm -i source RPM
Apply patch(es)
Tweak side-by-side config (if not done already)
Build & install binary RPM
(possibly copy binary, or data directory)
test / run / test /run...
rpm -e $( rpm -qa | cat - original.packages | sort | uniq -u )
and maybe remove a few files or folders (listed on the wiki page of course!)

And then it's done, with little hassle and no trace left behind.  Although maybe I'm missing some complexities here.  And while I know it's easy to suggest work for other people, it does seem that at least documenting it would really simplify things for casual or occasional builders or testers.  I can say for sure that if I'd found something like this documented I would have tested your patch.  Of course in this case it wouldn't have ended up doing any good... ;)

Cheers,
Ken


--
AGENCY Software  
A data system that puts you in control
100% Free Software
(253) 245-3801

learn more about AGENCY or
follow the discussion.