[GENERAL] select from table1 and table3 where (how table1 and table3 are related) is stored in table2?

[zach cruise]

see http://sqlfiddle.com/#!15/e30d9/8/0 for schema and sql.

http://stackoverflow.com/questions/12238621/sql-subquery-has-too-many-columns


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

[GENERAL] how would you speed up this long query?

[zach cruise]

select
  sub_query_1.pid,
  sub_query_1.tit,
  sub_query_1.num,
  sub_query_3.cid,
  sub_query_3.id,
  sub_query_3.c,
  sub_query_3.s,
  sub_query_3.z,
  sub_query_3.cy,
  sub_query_3.cd,
  sub_query_3.cr,
  org.id as org__id,
  org.pid as org__pid,
  org.open,
  org.cid as org__cid,
  z0.zcg
from
  (select
  proj.pid,
  proj.tit,
  proj.num
  from
  proj,
  (select
  org.pid
  from
  org
  where
  org.open = 'Y') as sub_1
  where
  proj.pid = sub_1.pid) as sub_query_1,
  (select
  detail.cid,
  detail.id,
  detail.c,
  detail.s,
  detail.z,
  detail.cy,
  detail.cd,
  detail.cr
  from
  detail,
  (select
  org.id
  from
  org
  where
  org.open = 'Y') as sub_3
  where
  detail.id = sub_3.id) as sub_query_3,
  org,
  z0
where
  sub_query_1.pid = org.pid and
  sub_query_3.id = org.id and
  sub_query_3.z = z0.zcg
group by
  z0.zcg,
  sub_query_1.pid,
  sub_query_1.tit,
  sub_query_1.num,
  sub_query_3.cid,
  sub_query_3.id,
  sub_query_3.c,
  sub_query_3.s,
  sub_query_3.z,
  sub_query_3.cy,
  sub_query_3.cd,
  sub_query_3.cr,
  org.id,
  org.pid,
  org.open,
  org.cid


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

Re: [GENERAL] how would you speed up this long query?

[zach cruise]

> Version of PostgreSQL?
9.3

> Operating system?
win

> Hardware configuration?
8 gb ram. takes about 7000 ms to retrieve about 7000 rows.
max_connections = 200
shared_buffers = 512mb
effective_cache_size = 6gb
work_mem = 13107kb
maintenance_work_mem = 512mb
checkpoint_segments = 32
checkpoint_completion_target = 0.7
wal_buffers = 16mb
default_statistics_target = 100

> Indexes?
no

> Anything else that might be relevant?
no

> What have you already done to investigate?
moved subquery from "where" to "from" to evaluate once instead of once per row

> EXPLAIN ANALYZE output?
would help if you can help us understand what's going on here:
"Group  (cost=5520.89..6335.03 rows=18092 width=199) (actual
time=3864.186..4402.447 rows=5512 loops=1)"
 "  ->  Sort  (cost=5520.89..5566.12 rows=18092 width=199) (actual
time=3864.171..4146.725 rows=97141 loops=1)"
 "Sort Key: z0.zcg, proj.pid, proj.tit, proj.num, detail.cid,
detail.id, det (...)"
 "Sort Method: external merge  Disk: 21648kB"
 "->  Hash Join  (cost=3541.48..4241.51 rows=18092 width=199)
(actual time=254.216..432.629 rows=97141 loops=1)"
 "  Hash Cond: (org.id = detail.id)"
 "  ->  Hash Join  (cost=752.72..1036.45 rows=4955
width=109) (actual time=64.492..86.822 rows=4977 loops=1)"
 "Hash Cond: (org.pid = proj.pid)"
 "->  Seq Scan on org  (cost=0.00..196.82
rows=4982 width=26) (actual time=0.024..6.199 rows=4982 loops=1)"
 "->  Hash  (cost=702.97..702.97 rows=3980
width=91) (actual time=64.439..64.439 rows=3973 loops=1)"
 "  Buckets: 1024  Batches: 1  Memory Usage: 465kB"
 "  ->  Hash Join  (cost=424.04..702.97
rows=3980 width=91) (actual time=20.994..52.773 rows=3973 loops=1)"
 "Hash Cond: (org_1.pid = proj.pid)"
 "->  Seq Scan on org org_1
(cost=0.00..209.28 rows=3980 width=8) (actual time=0.016..10.815
rows=3980 loops=1)"
 "  Filter: ((open)::text = 'Y'::text)"
 "  Rows Removed by Filter: 1002"
 "->  Hash  (cost=374.02..374.02
rows=4002 width=83) (actual time=20.950..20.950 rows=4002 loops=1)"
 "  Buckets: 1024  Batches: 1
Memory Usage: 424kB"
 "  ->  Seq Scan on proj
(cost=0.00..374.02 rows=4002 width=83) (actual time=0.010..9.810
rows=4002 loops=1)"
 "  ->  Hash  (cost=2716.44..2716.44 rows=5786 width=98)
(actual time=189.677..189.677 rows=4959 loops=1)"
 "Buckets: 1024  Batches: 1  Memory Usage: 629kB"
 "->  Hash Join  (cost=2369.71..2716.44 rows=5786
width=98) (actual time=169.635..182.956 rows=4959 loops=1)"
 "  Hash Cond: (org_2.id = detail.id)"
 "  ->  Seq Scan on org org_2
(cost=0.00..209.28 rows=3980 width=8) (actual time=0.015..4.194
rows=3980 loops=1)"
 "Filter: ((open)::text = 'Y'::text)"
 "Rows Removed by Filter: 1002"
 "  ->  Hash  (cost=2340.92..2340.92 rows=2303
width=90) (actual time=169.596..169.596 rows=1964 loops=1)"
 "Buckets: 1024  Batches: 1  Memory
Usage: 224kB"
 "->  Hash Join
(cost=2069.93..2340.92 rows=2303 width=90) (actual
time=159.126..166.937 rows=1964 loops=1)"
 "  Hash Cond: ((detail.z)::text =
(z0.zcg)::text)"
 "  ->  Seq Scan on detail
(cost=0.00..199.03 rows=2303 width=52) (actual time=0.009..2.152
rows=2303 loops=1)"
 "  ->  Hash
(cost=1538.30..1538.30 rows=42530 width=38) (actual
time=159.070..159.070 rows=42530 loops=1)"
 "Buckets: 8192  Batches:
1  Memory Usage: 2451kB"
 "->  Seq Scan on z0
(cost=0.00..1538.30 rows=42530 width=38) (actual time=0.010..82.125
rows=42530 loops=1)"
 "Total runtime: 4414.655 ms"


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

[GENERAL] better architecture?

[zach cruise]

i need some advice:

1. for our small business, i have a master (dev) - slave (prod) setup.
i develop using the master. i get data from other people's dev
(mssql) databases. i also get data from their prod (mssql) databases. i
replicate everything on slave.

apps using the master can connect only to dev databases, and apps using the
slave can connect only to prod databases. so when it's time to go from dev
to prod, i simply change that connection. no restart.

comments?

2. what happens if master-slave are rebooted at different times?

3. i also need to auto-promote slave to master if master fails (without
using repmgr or postgres-r or even postgres-xl). how?

Re: [GENERAL] better architecture?

[zach cruise]

combining replies for the list:


On 11/19/14, Charles Zaffery  wrote:
> 2 and 3 can be covered by this:
> http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster
does something similar exist for windows?


On 11/20/14, Michael Paquier  wrote:
> On Thu, Nov 20, 2014 at 10:58 AM, zach cruise  wrote:
>> 2. what happens if master-slave are rebooted at different times?
> What do you mean by that? If replication is impacted?
eg if i were to reboot the vmware running the master in the evening,
and reboot the vmware running the slave in the night, how would they
sync up?


On 11/20/14, Adrian Klaver wrote:
> What replication method are you using?
> The built in methods, Slony. Bucardo, etc?
built in

> The production users cannot enter or update records?
they can't. slave is read-only.

> If you have replication set up master -> slave, how can there be a difference 
> between the two?
there isn't. both contain dev and prod databases. users connect to the
dev databases from the dev web server, and to the prod databases from
the prod web server.

> Not sure where the mssql databases into this?
our corporate partners use them. when i need to query against them, i import.

>> 2. what happens if master-slave are rebooted at different times?
>>
>> 3. i also need to auto-promote slave to master if master fails (without 
>> using repmgr or postgres-r or even postgres-xl). how?
> Answers for 2 & 3 are dependent on answers to the above questions.


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

Re: [GENERAL] better architecture?

[zach cruise]

On 11/20/14, Adrian Klaver  wrote:
> On 11/20/2014 08:00 AM, zach cruise wrote:
>> combining replies for the list:
>>
>>
>> On 11/19/14, Charles Zaffery  wrote:
>>> 2 and 3 can be covered by this:
>>> http://clusterlabs.org/wiki/PgSQL_Replicated_Cluster
>> does something similar exist for windows?
>>
>>
>> On 11/20/14, Michael Paquier  wrote:
>>> On Thu, Nov 20, 2014 at 10:58 AM, zach cruise 
>>> wrote:
>>>> 2. what happens if master-slave are rebooted at different times?
>>> What do you mean by that? If replication is impacted?
>> eg if i were to reboot the vmware running the master in the evening,
>> and reboot the vmware running the slave in the night, how would they
>> sync up?
>
> Well it would depend on your setup and the load on the master. Assuming
> streaming replication. Simple explanation:
yes streaming replication.
>
> 1) If the master is down and slave is up then the slave will stall at
> whatever the last WAL was sent. When the master comes back up it will
> catch up as new WALs are generated.
>
> 2) If the slave is down and the master is up, the master will keep on
> creating WALs. The issue is that WALs are recycled over time, so given a
> significant load on the master and extended downtime for the slave it is
> possible that when the slave comes back up a WAL it needs is no longer
> available and it will start throwing errors. One way to tune this is
> modify wal_keep_segments (integer):
>
> http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html
while the WAL archive directory has to be shared with both master and
slave, should the WAL archive directory be independent of them ie
should it not go down with either of them? if it has to go down with
one, it seems it'd best for the WAL archive directory to go down with
slave?


>> On 11/20/14, Adrian Klaver wrote:
>>> What replication method are you using?
>>> The built in methods, Slony. Bucardo, etc?
>> built in
>>
>>> The production users cannot enter or update records?
>> they can't. slave is read-only.
>>
>>> If you have replication set up master -> slave, how can there be a
>>> difference between the two?
>> there isn't. both contain dev and prod databases. users connect to the
>> dev databases from the dev web server, and to the prod databases from
>> the prod web server.
>
> Crossed wires on my part, I was reading databases and thinking database
> clusters.
>
>>
>>> Not sure where the mssql databases into this?
>> our corporate partners use them. when i need to query against them, i
>> import.
>>
>>>> 2. what happens if master-slave are rebooted at different times?
>>>>
>>>> 3. i also need to auto-promote slave to master if master fails (without
>>>> using repmgr or postgres-r or even postgres-xl). how?
>>> Answers for 2 & 3 are dependent on answers to the above questions.
>
> For failover see:
>
> http://www.postgresql.org/docs/9.3/interactive/warm-standby-failover.html
>
> "PostgreSQL does not provide the system software required to identify a
> failure on the primary and notify the standby database server. Many such
> tools exist and are well integrated with the operating system facilities
> required for successful failover, such as IP address migration."
>
> So if you are looking for auto-promote you will need to look at third
> party tools or writing your own script.
while i can always use "pg_ctl promote", any recommendations for windows?


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

Re: [GENERAL] better architecture?

[zach cruise]

On 11/20/14, Adrian Klaver  wrote:
> On 11/20/2014 11:02 AM, zach cruise wrote:
>> On 11/20/14, Adrian Klaver  wrote:
>>> On 11/20/2014 08:00 AM, zach cruise wrote:
>>>> combining replies for the list:
>>>>
>
>>> Well it would depend on your setup and the load on the master. Assuming
>>> streaming replication. Simple explanation:
>> yes streaming replication.
>>>
>>> 1) If the master is down and slave is up then the slave will stall at
>>> whatever the last WAL was sent. When the master comes back up it will
>>> catch up as new WALs are generated.
>>>
>>> 2) If the slave is down and the master is up, the master will keep on
>>> creating WALs. The issue is that WALs are recycled over time, so given a
>>> significant load on the master and extended downtime for the slave it is
>>> possible that when the slave comes back up a WAL it needs is no longer
>>> available and it will start throwing errors. One way to tune this is
>>> modify wal_keep_segments (integer):
>>>
>>> http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html
>> while the WAL archive directory has to be shared with both master and
>> slave, should the WAL archive directory be independent of them ie
>> should it not go down with either of them? if it has to go down with
>> one, it seems it'd best for the WAL archive directory to go down with
>> slave?
>
> So I am to understand that you have WAL archiving set up also?
yes, slave gets updates from stream and WAL. if either fails, the
other will update.

> Again a simplified version:
>
> The ideal situation is you have a third machine that has the WAL
> archives. The issue is that if the master cannot archive a WAL it will
> keep it around until it can. So depending on load and outage you can end
> with disk space issues on the master should it not be able clear the WALs.
>
> For more info see:
>
> http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html
to be clear- i change my 2 VMs setup {"1. master (dev) - 2. slave
(prod) setup"} to 3 VMs {"1. master (dev) - 2. slave (prod) setup - 3.
archive (wal)"}.

but what do i gain?

as it is, in the worst case, VMs can always be restored "fairly
quickly" for our use.

>>> For failover see:
>>>
>>> http://www.postgresql.org/docs/9.3/interactive/warm-standby-failover.html
>>>
>>> "PostgreSQL does not provide the system software required to identify a
>>> failure on the primary and notify the standby database server. Many such
>>> tools exist and are well integrated with the operating system facilities
>>> required for successful failover, such as IP address migration."
>>>
>>> So if you are looking for auto-promote you will need to look at third
>>> party tools or writing your own script.
>> while i can always use "pg_ctl promote", any recommendations for windows?
>
> Not from me, I do not run Postgres on Windows so I will be of no help
> there.


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

Re: [GENERAL] better architecture?

[zach cruise]

On 11/20/14, Adrian Klaver  wrote:
> On 11/20/2014 12:30 PM, zach cruise wrote:
>>>
>>> For more info see:
>>>
>>> http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html
>> to be clear- i change my 2 VMs setup {"1. master (dev) - 2. slave
>> (prod) setup"} to 3 VMs {"1. master (dev) - 2. slave (prod) setup - 3.
>> archive (wal)"}.
>>
>> but what do i gain?
>
> Extra protection against failure, maybe.
>
> So:
>
>--->  WAL Archive ---
>||
>|Streaming   |
> master ---  > slave
>
> If the direct link between the master and slave goes down, the slave can
> still get WALs from the archive. If the archive machine goes down you
> still have the direct link. If you take the slave down the master can
> still push WALs to the archive. This assumes the 'machines' are actually
> separated and connecting through different networks. You say you are
> using VMs, but not where they are running. If they are all running on
> the same machine running through the same network link then you really
> do not have protection against network issues. The same if the host
> machine goes down. This is one of those pen and paper times, when you
> sketch out the arrangement and start doing what ifs.

master, slave and archive can be 3 separate VMs on 1 host, with their
clones on 2nd and 3rd hosts.

a follow-up question on WAL recycling: ("When WAL archiving is being
done, the log segments must be archived before being recycled or
removed" from http://www.postgresql.org/docs/9.3/static/wal-configuration.html)

say streaming is off-
* if both master and archive are down, slave is still up and running. yes?
* if master writes data when archive is down, it will copy over to
slave when archive is back up. yes?
* but if WAL is recycled before archive is back up, it will not copy
over to slave. yes?
see my concern with a separate archive is if archive is down and
master gets stuck retrying to push the same segment again and again,
there may be a problem in recovery when archive is back up. no?


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

Re: [GENERAL] better architecture?

[zach cruise]

On 11/20/14, Adrian Klaver  wrote:
> On 11/20/2014 04:57 PM, zach cruise wrote:
>> On 11/20/14, Adrian Klaver  wrote:
>>> On 11/20/2014 12:30 PM, zach cruise wrote:
>>>>>
>>>>> For more info see:
>>>>>
>>>>> http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html
>>>> to be clear- i change my 2 VMs setup {"1. master (dev) - 2. slave
>>>> (prod) setup"} to 3 VMs {"1. master (dev) - 2. slave (prod) setup - 3.
>>>> archive (wal)"}.
>>>>
>>>> but what do i gain?
>>>
>>> Extra protection against failure, maybe.
>>>
>>> So:
>>>
>>> --->  WAL Archive ---
>>> ||
>>> |Streaming   |
>>> master ---  > slave
>>>
>>> If the direct link between the master and slave goes down, the slave can
>>> still get WALs from the archive. If the archive machine goes down you
>>> still have the direct link. If you take the slave down the master can
>>> still push WALs to the archive. This assumes the 'machines' are actually
>>> separated and connecting through different networks. You say you are
>>> using VMs, but not where they are running. If they are all running on
>>> the same machine running through the same network link then you really
>>> do not have protection against network issues. The same if the host
>>> machine goes down. This is one of those pen and paper times, when you
>>> sketch out the arrangement and start doing what ifs.
>
> First of all, the below is really in need of whiteboard/paper diagram to
> keep track of the moving parts. That being said here it goes:
>
>>
>> master, slave and archive can be 3 separate VMs on 1 host, with their
>> clones on 2nd and 3rd hosts.
>
> I can see the above being a potential nightmare. I am not sure how you
> ensure that the cloning process results in clones that exactly mirror
> the state of the originals at a particular point in time. Failing in
> that would seem to me to lead to no end of issues in the replication
> process.
>
>>
>> a follow-up question on WAL recycling: ("When WAL archiving is being
>> done, the log segments must be archived before being recycled or
>> removed" from
>> http://www.postgresql.org/docs/9.3/static/wal-configuration.html)
>>
>> say streaming is off-
>> * if both master and archive are down, slave is still up and running.
>> yes?
>
> Yes.
>
>> * if master writes data when archive is down, it will copy over to
>> slave when archive is back up. yes?
>
> If streaming is off and you are doing archiving then it will copy over
> to the archive.
>
>> * but if WAL is recycled before archive is back up, it will not copy
>> over to slave. yes?
>
> The issue here as pointed out previously is that the WALs will stack up
> on the master because it will not be able to archive them. So then you
> run into a potential of of space issue on the master. From here:
>
> http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html
>
> "It is important that the archive command return zero exit status if and
> only if it succeeds. Upon getting a zero result, PostgreSQL will assume
> that the file has been successfully archived, and will remove or recycle
> it. However, a nonzero status tells PostgreSQL that the file was not
> archived; it will try again periodically until it succeeds."
>
> ...
>
> "While designing your archiving setup, consider what will happen if the
> archive command fails repeatedly because some aspect requires operator
> intervention or the archive runs out of space. For example, this could
> occur if you write to tape without an autochanger; when the tape fills,
> nothing further can be archived until the tape is swapped. You should
> ensure that any error condition or request to a human operator is
> reported appropriately so that the situation can be resolved reasonably
> quickly. The pg_xlog/ directory will continue to fill with WAL segment
> files until the situation is resolved. (If the file system containing
> pg_xlog/ fills up, PostgreSQL will do a PANIC shutdown. No committed
> transactions will be lost, but the database will remain offline until
> you free some space.)"
>
>
>> see my concern with a separate archive is if archive is down and
>> master gets stuck retrying to push the same segment again and again,
>> there may be a problem in recovery when archive is back up. no?
>
> See above.

also streaming is working (checked with wireshark) and WAL is being
written to by master, but if i comment out streaming in pg_hba.conf,
WAL is not copying over to slave?!

how can i verify that WAL archiving is working for slave?


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

Re: [GENERAL] better architecture?

[zach cruise]

On 11/22/14, Adrian Klaver  wrote:
> On 11/21/2014 07:38 PM, zach cruise wrote:
>> On 11/20/14, Adrian Klaver  wrote:
>>> On 11/20/2014 04:57 PM, zach cruise wrote:
>>>> On 11/20/14, Adrian Klaver  wrote:
>>>>> On 11/20/2014 12:30 PM, zach cruise wrote:
>>>>>>>
>>>>>>> For more info see:
>>>>>>>
>>>>>>> http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html
>>>>>> to be clear- i change my 2 VMs setup {"1. master (dev) - 2. slave
>>>>>> (prod) setup"} to 3 VMs {"1. master (dev) - 2. slave (prod) setup -
>>>>>> 3.
>>>>>> archive (wal)"}.
>>>>>>
>>>>>> but what do i gain?
>>>>>
>>>>> Extra protection against failure, maybe.
>>>>>
>>>>> So:
>>>>>
>>>>>  --->  WAL Archive ---
>>>>>  ||
>>>>>  |Streaming   |
>>>>> master ---  > slave
>>>>>
>>>>> If the direct link between the master and slave goes down, the slave
>>>>> can
>>>>> still get WALs from the archive. If the archive machine goes down you
>>>>> still have the direct link. If you take the slave down the master can
>>>>> still push WALs to the archive. This assumes the 'machines' are
>>>>> actually
>>>>> separated and connecting through different networks. You say you are
>>>>> using VMs, but not where they are running. If they are all running on
>>>>> the same machine running through the same network link then you really
>>>>> do not have protection against network issues. The same if the host
>>>>> machine goes down. This is one of those pen and paper times, when you
>>>>> sketch out the arrangement and start doing what ifs.
>>>
>>> First of all, the below is really in need of whiteboard/paper diagram to
>>> keep track of the moving parts. That being said here it goes:
>>>
>>>>
>>>> master, slave and archive can be 3 separate VMs on 1 host, with their
>>>> clones on 2nd and 3rd hosts.
>>>
>>> I can see the above being a potential nightmare. I am not sure how you
>>> ensure that the cloning process results in clones that exactly mirror
>>> the state of the originals at a particular point in time. Failing in
>>> that would seem to me to lead to no end of issues in the replication
>>> process.
>>>
>>>>
>>>> a follow-up question on WAL recycling: ("When WAL archiving is being
>>>> done, the log segments must be archived before being recycled or
>>>> removed" from
>>>> http://www.postgresql.org/docs/9.3/static/wal-configuration.html)
>>>>
>>>> say streaming is off-
>>>> * if both master and archive are down, slave is still up and running.
>>>> yes?
>>>
>>> Yes.
>>>
>>>> * if master writes data when archive is down, it will copy over to
>>>> slave when archive is back up. yes?
>>>
>>> If streaming is off and you are doing archiving then it will copy over
>>> to the archive.
>>>
>>>> * but if WAL is recycled before archive is back up, it will not copy
>>>> over to slave. yes?
>>>
>>> The issue here as pointed out previously is that the WALs will stack up
>>> on the master because it will not be able to archive them. So then you
>>> run into a potential of of space issue on the master. From here:
>>>
>>> http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html
>>>
>>> "It is important that the archive command return zero exit status if and
>>> only if it succeeds. Upon getting a zero result, PostgreSQL will assume
>>> that the file has been successfully archived, and will remove or recycle
>>> it. However, a nonzero status tells PostgreSQL that the file was not
>>> archived; it will try again periodically until it succeeds."
>>>
>>> ...
>>>
>>> "While designing your archiving setup, consider what will happen if the
>>> archive command fails repeatedly because some aspect requires operator
>>> intervention or the archive runs out of space. For example, this could
>>> occur if you write to tape without a

[GENERAL] which is better- storing data as array or json?

[zach cruise]

for indexing, accessing, filtering and searching?

as simple array-
first name  | last name | nicknames
tom | jerry | {cat}, {mouse}

as multi-dimensional array-
first name  | last name | nicknames
tom | jerry | {cat, kat}, {mouse, mice}

as simple json-
first name  | last name | nicknames
tom | jerry | {"public": "cat", "private": "mouse"}

as multi-nested json-
first name  | last name | nicknames
tom | jerry | {"public": {"first": "cat", "second": "kat"},
"private": {"first": "mouse", "second": "mice"}}


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

Re: [GENERAL] which is better- storing data as array or json?

[zach cruise]

i can't keep creating tables or adding columns every time i need to
add a nickname- this happens a lot.

so i want to put everything in an array or json.

remember rows can have different number of nicknames.


On 2/17/15, David G Johnston  wrote:
> zach cruise wrote
>> for indexing, accessing, filtering and searching?
>>
>> as simple array-
>> first name   | last name | nicknames
>> tom | jerry | {cat}, {mouse}
>>
>> as multi-dimensional array-
>> first name   | last name | nicknames
>> tom | jerry | {cat, kat}, {mouse, mice}
>>
>> as simple json-
>> first name   | last name | nicknames
>> tom | jerry | {"public": "cat", "private": "mouse"}
>>
>> as multi-nested json-
>> first name   | last name | nicknames
>> tom | jerry  | {"public": {"first": "cat", "second": "kat"},
>> "private": {"first": "mouse", "second": "mice"}}
>
> The choice of proper model depends on how you intend to make use of it.
>
> That said, I'd go with "none of the above" by default.
>
> My first reaction in this scenario would be to create a nicknames table:
>
> [nick_person_id, person_id, nick_name, nick_scope, nick_scope_order]
>
> You could maybe normalize further by having a nickname table with integer
> keys that then end up as FKs on this many-to-many relation.
>
> An array is too complicated given the fact you need to track attributes on
> the nicknames.  You could possible do an array over a composite type but
> I'm
> not sure how indexing and searching would fare in that setup.
>
> Why are you even considering storing the information in JSON?  The answer
> to
> that question would make it more obvious whether that solution is viable
> but
> do you really want any application that makes use of this data to have to
> speak JSON to do so when the time-tested relational model can likely give
> you everything you need - and probably more.  Even if you had to serialize
> the data to and from JSON I would say that storing the data in that format
> to avoid the serializing is an instance of pre-mature optimization.
>
> David J.
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/which-is-better-storing-data-as-array-or-json-tp5838358p5838362.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


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

[GENERAL] select where true, or select where input = '$var'

[zach cruise]

i want to select based on input, but if input is not provided or if
input is empty, then i want to select all rows.

1 select *
2 from table
3 if input = '' then
4  where true
5 else
6  where input = '$sanitized_variable'
7 end if;
(syntax error at 3)

i also looked at 'case' but i don't think it applies here.

http://www.postgresql.org/docs/9.3/static/functions-conditional.html
http://dba.stackexchange.com/questions/41067/getting-select-to-return-a-constant-value-even-if-zero-rows-match


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

Re: [GENERAL] select where true, or select where input = '$var'

[zach cruise]

sorry, corrected below:

1 select *
2 from table
3 if '$sanitized_variable' = '' then -- $variable not provided or
undefined or empty,
4  where true -- so select every row/record
5 else
6  where input = '$sanitized_variable' -- variable provided or defined
or not-empty, so select only matching rows/records where input is a
column/field
7 end if;


On 2/19/15, John R Pierce  wrote:
> On 2/19/2015 12:39 PM, zach cruise wrote:
>> i want to select based on input, but if input is not provided or if
>> input is empty, then i want to select all rows.
>
> something unclear here, is INPUT a variable in your application program,
> or is it a field in the table?
>
>
>
> --
> john r pierce  37N 122W
> somewhere on the middle of the left coast
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


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

[GENERAL] upgrading to 9.3

[zach cruise]

moving from 8.1 to 9.3, and redesigning at the same time (using navicat and
psql).

have access to both 8.1 and 9.3. and by redesigning i mean, going from
multiple databases to multiple schemas.

so what's the best approach?

Re: [GENERAL] upgrading to 9.3

[zach cruise]

thanks andy!

a little off-topic, but about database reorganization - is it recommended
to group all sequences and domains under the public schema? or is a
sequence tied to one table and is better in its separate schema?

what about replication options for x64 systems since slony is not an option?

[GENERAL] database redesign

[zach cruise]

my response hasn't shown up on
http://postgresql.1045698.n5.nabble.com/upgrading-to-9-3-td5777291.html so
trying again. sorry if both show up.

anyway, on database reorganization - is it recommended to group all
sequences and domains under one public schema? or is a sequence tied to a
table as its counter?

what are some replication choices for x64 systems since slony is not an
option?

[GENERAL] file system level backup

[zach cruise]

so i installed 9.3 x64 on two windows 2008 servers from one set of
installation files. then i created databases on postgresql a, shutdown all
the servers, copied /data/ (from "show data_directory") from postgresql a
to postgresql b. in short, i did a full file system level backup (
http://www.postgresql.org/docs/9.3/static/backup-file.html).

however, postgresql b won't start ("not listening")?

so i restored postgresql b's old /data/, but it still won't start?!

help!

(longer answer - what's the best way to copy data from a to b?)

Re: [GENERAL] file system level backup

[zach cruise]

Questions:

1) Both servers are x64?

> yes

2) What installation files(Graphical installer, source,etc)?

> postgresql-9.3.0-1-windows-x64 from enterprisedb.com

3) Does the data directory have the config files?

> yes

however, postgresql b won't start ("not listening")?

4) How are you starting?

> pgadmin -> start server

5) What is the exact error message and where are you seeing it?

> "server not listening on port 5432" in cmd ('netstat -ab | find "5432"'
also returns empty)

so i restored postgresql b's old /data/, but it still won't start?!

6) So there was an existing Postgres instance at b?

> yes. two installations - a and b.

7) What version was it?

> both are 9.3.0-1 from the same installation file (above).

fyi, pg_bouncer process was running even after the server shutdown when i
did the copy (since size is not an issue, i could copy the entire
/postgresql/?).

Re: [GENERAL] file system level backup

[zach cruise]

Questions:

1) Both servers are x64?

 > yes

2) What installation files(Graphical installer, source,etc)?

 > postgresql-9.3.0-1-windows-x64 from enterprisedb.com


3) Does the data directory have the config files?

 > yes

 however, postgresql b won't start ("not listening")?

4) How are you starting?

 > pgadmin -> start server

5) What is the exact error message and where are you seeing it?

 > "server not listening on port 5432" in cmd ('netstat -ab | find
"5432"' also returns empty)

Have you checked what port is set up in postgresql.conf?

> yes (port = 5432)

I am not that familiar with Windows and pgAdmin so I will resort to the
docs:

Does any of the below apply?

http://www.pgadmin.org/docs/1.8/connect.html

"If pgAdmin is running on a Windows machine, it can control the postmaster
service if you have enough access rights. Enter the name of the service. In
case of a remote server, it must be prepended by the machine name (e.g.
PSE1\pgsql-8.0). pgAdmin will automatically discover services running on
your local machine. "

> thanks. it doesn't help. i also tried pg_ctl without luck (
http://www.postgresql.org/docs/9.3/static/app-pg-ctl.html).

 so i restored postgresql b's old /data/, but it still won't start?!

6) So there was an existing Postgres instance at b?

 > yes. two installations - a and b.

I am still not understanding. You have a 2008 server at a and one at b. You
copied the Postgres data files from 2008 server a to  2008 server b. This
did not work, so you say you restored the data directory on server b from
an old copy of the data directory at b.

So where did that old copy come from?

> short answer - from b itself (whatever was created right after
installation).

> long answer - 2008 servers at a and b. postgres 9.3s at a and b. added
databases to postgres-a (postgres-b is empty/new). shutdown postgres-a and
b. copied data-a and b to a network shared between server-a and b. deleted
data-b on server-b. copied data-a from network to server-b.

> restarted postgres-a and postgres-b. postgres-a succeeds. postgres-b
fails.

> so deleted data on server-b. restored data-b from network ("old copy") to
server-b. restarted postgres-b. postgres-b still fails.

Re: [GENERAL] file system level backup

[zach cruise]

I am still not understanding. You have a 2008 server at a and one at b.
You copied the Postgres data files from 2008 server a to  2008 server b.
This did not work, so you say you restored the data directory on server
b from an old copy of the data directory at b.

So where did that old copy come from?

> short answer - from b itself (whatever was created right after
installation).

> long answer - 2008 servers at a and b. postgres 9.3s at a and b.
added databases to postgres-a (postgres-b is empty/new). shutdown
postgres-a and b. copied data-a and b to a network shared between
server-a and b. deleted data-b on server-b. copied data-a from network
to server-b.

> restarted postgres-a and postgres-b. postgres-a succeeds. postgres-b
fails.

> so deleted data on server-b. restored data-b from network ("old
copy") to server-b. restarted postgres-b. postgres-b still fails.

So is there anything in the Windows system logs?

> looks like file system level backups don't work well on windows because
they get corrupted during transfer.

Does the Task Manager show another Postgres running?

> no

Re: [GENERAL] file system level backup

[zach cruise]

I am not sure if you have already answered it and I have somehow missed it:
- Are these 'a' and 'b' on two different servers? ( I think they are on
different servers)

> yes

- Did you stop the server on 'b' before you replaced the files and
attempted a startup?

> yes

> thanks. it doesn't help. i also tried pg_ctl without luck (
http://www.postgresql.org/docs/9.3/static/app-pg-ctl.html).

What exact pg_ctl command you used?

> "pg_ctl start" and "pg_ctl stop"

---

 So is there anything in the Windows system logs?

 > looks like file system level backups don't work well on windows
 because they get corrupted during transfer.

Whoa, how did we get to that conclusion?

 > as a safety, i had also renamed old data-b. when copying old data-b
from the network didn't work, i renamed it back. and that worked.

Well all that proves is that there is some difference between the data
directories. That could be corruption, it could be something else.

Since we are on the subject of corruption and this is Windows, do you have
anti-virus software running on these machines?

If so, is it pointed at the directories in question?

> yes (symantec) but it is pointed to both directories, old data-b and
renamed old data-b, so i doubt if that is the issue.

[GENERAL] design question: how to geocode multiple dynamic "city, country"?

[zach cruise]

i accept multiple "city, country" from users on-the-fly, and want to
dynamically map them.

i could create a table where i insert their multiple entries, and then
geocode that table for display.

but i also want to avoid giving write permission to the web user.

i could create a schema and restrict write to that schema.

or something better?


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

[GENERAL] what happens to postmaster?

[zach cruise]

...when i am importing a table (from oracle, or updating it), and a
user queries that same table?

is it ok to be concerned about corruption etc.

using 8.1.

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

Re: [GENERAL] what happens to postmaster?

[zach cruise]

using navicat 8's import wizard which does row inserts
(provider=msdaora.1; persist security info=true). more
http://www.navicat.com/manual/online_manual/win_manual/ImportODBC.html

i know this is not navicat support, but can anyone from navicat
support also try answer this question?

On Tue, Jun 22, 2010 at 2:21 PM, Bill Moran  wrote:
> In response to zach cruise :
>
>> ...when i am importing a table (from oracle, or updating it), and a
>> user queries that same table?
>
> I depends on a lot of information you haven't provided ... Is the
> update inside a transaction?  Have you locked the table?
>
> The real answer is: whatever you want to happen.  Depending on how you
> set up the import, you have control over how others can interact with
> the table while the import is running.
>
> Some examples:
> * Grab an access exclusive lock on the table, nobody else will be able
>  to access it until you're done.
> * Grab an exclusive lock on the table, others will be able to read, but not
>  write to it until you're done.
>
> Some documentation to read up on:
> http://www.postgresql.org/docs/8.4/static/explicit-locking.html
>
>> is it ok to be concerned about corruption etc.
>
> You should always be concerned about those things.
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

Re: [GENERAL] MySQL versus Postgres

[zach cruise]

john, you're running up against a culture here, and trying to answer
the question: how to make a nerd cool? answer: it can't be done.

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

[GENERAL] ease of use sync

[zach cruise]

on projects where i ended up selecting oracle, *my* main reasons were
(1) clustering/replication
(2) cross-database query
(3) promise of drcp
in that order

for (1), actually more for synchronization/transfer, i got a simple
suggestion:
while installing postgresql, why not ask the user to give ip of a 2nd remote
machine with the right ports open on which the installer can then repeat the
exact installation, prompt for sync frequency, and do a
pg_dumpall/pg_restore based on that frequency. so by the time you are done
installing, you got a "stand-by"! repeat that for upgrades.

it is easy to do manually (setup cron, navicat etc) but far easier when
combined during postgresql installation.

[GENERAL] how to look for duplicate rows?

[zach cruise]

i have to clean a table that looks like so:

create table test (sn integer, fname varchar(10), lname varchar(10));
insert into test values (1, 'adam', 'lambert');
insert into test values (2, 'john', 'mayer');
insert into test values (3, 'john', 'mayer');
insert into test values (4, 'mary', 'kay');
insert into test values (5, 'john', 'mayer');
insert into test values (6, 'susan', 'boyle');
insert into test values (7, 'susan', 'boyle');
insert into test values (8, 'mark', 'ingram');

for that, i need to run a query that returns like so:

result:
is_not_distinct
2, 3, 5
6, 7

using 8.1.

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

[GENERAL] (odbc) multiple step ole db generated error - date/timestamp column

[zach cruise]

when importing from oracle 10g, i get "multiple step ole db generated
error". i narrowed this down to a date/timestamp column - actually to
about 100 rows within that column, all = '01-JAN-01' (never null).

there is another date/timestamp column that gets imported error-free,
and other tables also get imported fine, so i am stumped?

query log not very helpful. using 8.1 with navicat.

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

Re: [GENERAL] (odbc) multiple step ole db generated error - date/timestamp column

[zach cruise]

On Sun, Mar 22, 2009 at 3:39 AM, Craig Ringer
 wrote:
> zach cruise wrote:
>> when importing from oracle 10g
>
> Importing how? CSV dump and load? DB link of some sort?

odbc (see email) specifically Microsoft OLE DB Provider for Oracle

> Operating system and version? Oracle version?

windows 2k3
oracle 10g (see email)
postgresql 8.1 (see email)

>> i get "multiple step ole db generated
>> error".
>
> From what program ? Where?

navicat (see email)

again, i narrowed this down to a date/timestamp column that gets
imported ok if imported as varchar. there is another date/timestamp
column that gets imported error-free, and other tables also get
imported ok. based on my narrowing-down, it appears less likely error
is at os/odbc/postgresql/oracle level but more likely at
database/table/column/row level (esp since all problem rows =
'01-JAN-01' (never null) but that could be a false-alarm).

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

Re: [GENERAL] (odbc) multiple step ole db generated error - date/timestamp column

[zach cruise]

ok i brought it in as varchar and cast as date.

On Sun, Mar 22, 2009 at 12:27 PM, zach cruise  wrote:
> On Sun, Mar 22, 2009 at 3:39 AM, Craig Ringer
>  wrote:
>> zach cruise wrote:
>>> when importing from oracle 10g
>>
>> Importing how? CSV dump and load? DB link of some sort?
>
> odbc (see email) specifically Microsoft OLE DB Provider for Oracle
>
>> Operating system and version? Oracle version?
>
> windows 2k3
> oracle 10g (see email)
> postgresql 8.1 (see email)
>
>>> i get "multiple step ole db generated
>>> error".
>>
>> From what program ? Where?
>
> navicat (see email)
>
> again, i narrowed this down to a date/timestamp column that gets
> imported ok if imported as varchar. there is another date/timestamp
> column that gets imported error-free, and other tables also get
> imported ok. based on my narrowing-down, it appears less likely error
> is at os/odbc/postgresql/oracle level but more likely at
> database/table/column/row level (esp since all problem rows =
> '01-JAN-01' (never null) but that could be a false-alarm).

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

[GENERAL] ...where 'value' = array[]

[zach cruise]

i have table, like so:
group.group_name (varchar) | group.group_array (varchar[])
-
west coast | {CA,WA}
east coast | {NY,MA}

i can do this:
select group_name from group where 'CA' = any(array['CA','WA']);

but i need to select group_name where state_abbreviation is in
group_array, something like:
select group_name from group where 'CA' = any(group_array);
or
select group_name from group where 'CA' in (group_array);
or
select group_name from group where 'CA' in (select group_array from
group where true);

all ()[]{} have me confused
http://www.nabble.com/IN-with-arrays-td10011058.html

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

[GENERAL] how to search for relation by name?

[zach cruise]

when i try to copy database (into another database), i get "relation
does not exist" errors for 'super objects' like sequences. (that is
fine since i am using pg_dump, not pg_dumpall) but there is one
relation i can't find to recreate in the new database.

how can i search database for relation by name (some catalog table)?

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

Re: [GENERAL] how to search for relation by name?

[zach cruise]

Thanks! I just created new database using old database as template,
and everything got copied over without errors. is that recommended
over pg_dump though when just recreating database for different
encoding?

http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html

On Wed, Apr 22, 2009 at 6:14 PM, DM  wrote:
> Here is the sql
> SELECT c.oid,
>   n.nspname,
>   c.relname
> FROM pg_catalog.pg_class c
>      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relname LIKE ('%dt%')
>   AND pg_catalog.pg_table_is_visible(c.oid)
> replace dt with your sequence name
> pg_catalog has the information.
> Thanks
> Deepak
>
> On Wed, Apr 22, 2009 at 2:36 PM, zach cruise  wrote:
>>
>> when i try to copy database (into another database), i get "relation
>> does not exist" errors for 'super objects' like sequences. (that is
>> fine since i am using pg_dump, not pg_dumpall) but there is one
>> relation i can't find to recreate in the new database.
>>
>> how can i search database for relation by name (some catalog table)?
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general

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

[GENERAL] utf8 encoding for template0 and template1?

[zach cruise]

is it recommended to change encodings for template0 and template1 to
utf8 (by recreating databases) for 8.1?

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