Re: [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints

2017-10-04 Thread Andreas Joseph Krogh
På onsdag 04. oktober 2017 kl. 00:24:19, skrev Vik Fearing <
vik.fear...@2ndquadrant.com <mailto:vik.fear...@2ndquadrant.com>>:
On 10/03/2017 10:10 PM, Andreas Joseph Krogh wrote:
 > While we're in deferrable constraints land...;
 > I even more often need deferrable /conditional /unique-indexes.
 > In PG you now may have:
 >
 > ALTER TABLE email_folder ADD CONSTRAINT some_uk UNIQUE (owner_id, 
folder_type, name) DEFERRABLE INITIALLY DEFERRED;
 >
 > 
 > But this isn't supported:
 >
 > CREATE UNIQUE INDEX some_uk ON email_folder(owner_id, folder_type, name) 
WHERE parent_id IS NULL DEFERRABLE INITIALLY DEFERRED;
 >
 > Are there any plans to support this?

 I don't want to hijack the thread, but you can do that with exclusion
 constraints.
 
 
True.
 
--
 Andreas Joseph Krogh
 




Re: [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints

2017-10-03 Thread Andreas Joseph Krogh
På tirsdag 03. oktober 2017 kl. 21:51:30, skrev Nico Williams <
n...@cryptonector.com <mailto:n...@cryptonector.com>>:
Attached are patches to add an ALWAYS DEFERRED option to CONSTRAINTs and
 CONSTRAINT TRIGGERs, meaning: SET CONSTRAINTS .. IMMEDIATE will not make
 immediate any constraint/trigger that is declared as ALWAYS DEFERRED.

 I.e., the opposite of NOT DEFERRED.  Perhaps I should make this NOT
 IMMEDIATE?  Making it NOT IMMEDIATE has the benefit of not having to
 change the precedence of ALWAYS to avoid a shift/reduce conflict...  It
 may also be more in keeping with NOT DEFERRED.

 Motivation:

  - I have trigger procedures that must run at the end of the transaction
    (after the last statement prior to COMMIT sent by the client/user),
    which I make DEFERRABLE, INITIALLY DEFERRED CONSTRAINT TRIGGERs out
    of, but SET CONSTRAINTS can be used to foil my triggers.  I have
    written SQL code to detect that constraint triggers have fired too
    soon, but I'd rather not need it.

  - Symmetry.  If we can have NOT DEFERRABLE constraints, why not also
    NOT IMMEDIABLE?  :)  Naturally "immediable" is not a word, but you
    get the point.

  - To learn my way around PostgreSQL source code in preparation for
    other contributions.

 Anyways, this patch is NOT passing tests at the moment, and I'm not sure
 why.  I'm sure I can figure it out, but first I need to understand the
 failures.  E.g., I see this sort of difference:

    \d testschema.test_index1
    Index "testschema.test_index1"
     Column |  Type  | Definition
    ++
     id     | bigint | id
   -btree, for table "testschema.test_default_tab"
   +f, for table "testschema.btree", predicate (test_default_tab)

 which means, I think, that I've screwed up in src/bin/psql/describe.c,
 don't it's not obvious to me yet how.

 Some questions for experienced PostgreSQL developers:

 Q0: Is this sort of patch welcomed?

 Q1: Should new columns for pg_catalog.pg_constraint go at the end, or may
     they be added in the middle?

 Q2: Can I add new columns to information_schema tables, or are there
     standards-compliance issues with that?

 Q3: Is the C-style for PG documented somewhere?  (sorry if I missed this)

 Q4: Any ideas what I'm doing wrong in this patch series?

 Nico
 
 
+1.
 
While we're in deferrable constraints land...;  I even more often need 
deferrable conditional unique-indexes.
In PG you now may have:
ALTER TABLE email_folder ADD CONSTRAINT some_uk UNIQUE (owner_id, folder_type, 
name) DEFERRABLE INITIALLY DEFERRED; 
 
But this isn't supported:
CREATE UNIQUE INDEX some_uk ON email_folder(owner_id, folder_type, name) WHERE 
parent_idIS NULL DEFERRABLE INITIALLY DEFERRED;  

Are there any plans to support this?
 
Thanks.



 
--
 Andreas Joseph Krogh
 




Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-18 Thread Andreas Joseph Krogh
På mandag 18. september 2017 kl. 16:28:07, skrev Bruce Momjian <br...@momjian.us
 <mailto:br...@momjian.us>>:
On Sat, Sep 16, 2017 at 11:36:40PM +0200, Andreas Joseph Krogh wrote:
 > På lørdag 16. september 2017 kl. 18:34:51, skrev Bruce Momjian <
 > br...@momjian.us>:
 >     No.  If you ran initdb with --waldir on the new primary, you will create
 >     a symbolic link in the PGDATA directory, and a directory outside of
 >     PGDATA for storing the WAL.  When you run rsync on the new primary
 >     PGDATA directory, you will copy the symlink in the PGDATA directory, but
 >     it will point to probably nothing on the standby.
 >
 >  
 >  
 > The misunderstanding here comes from the fact that I used pg_upgradecluster
 > like this:
 > pg_upgradecluster --method=upgrade --link 9.6 main
 >  
 > and it didn't issue initdb with --waldir on the new cluster (because
 > pg_upgradecluster issues initdb for you), so pg_wal ended up in $PGDIR 
because
 > pg_upgradecluster didn't figure out the old cluster was initialized with
 > --xlogdir. This is why I thought i made sense mentioning that one had to 
move
 > pg_wal manually.
 >  
 > I know it's debian-stuff, but maybe it's worth mentioning pg_upgradecluster
 > somewhere and recommend not using it? It seems to start the new cluster
 > automatically and when upgrading standbys section 10 tells you not to do 
that.

 So you didn't really follow the instructions, but instead are trying to
 use the standby part of the instructions and found a problem with the
 way pg_upgradecluster handled it.  We really can't document this.

 It would be good to report the bug to pg_upgradecluster developers
 though.

 Yes, I can see rsync not working that case.
 
 
 
Actually I didn't know about --waldir switch of initdb and have always moved 
pg_xlog manually then symlinking. 
 
 
 
>     > should be clearly pointed out that copying pg_wal is only needed in 
those
 >     > cases, and that it can be done with whatever network-copying procedure
 >     you're
 >     > familiar with, ie. scp/rsync. This step is not similar to the steps
 >     required
 >     > for copying tablespaces outside $PGDATA, so it's worth documenting
 >     explicitly.
 >     > Maybe also telling users to ensure the synlink (in $PGDATA) to pg_wal 
on
 >     > standby points to pg_wal.
 >
 >     Why tell them new instructions when the rsync instructions work fine?
 >     What is the value?
 >
 >  
 > The rsync instructions mentioned in 10.F all address the --link scenario and
 > use "--delete --hard-links --size-only", and "merge 2 source-dirs into one",
 > which isn't relevant when copying pg_wal.
 >  
 > This sentence:
 > "If you have relocated pg_wal outside the data directories, rsync must be 
run
 > on those directories too."
 > implies one must follow the rsync pattern elsewhere in 10.F, which isn't 
really
 > true. Maybe re-wording it to:
 > "If you have relocated pg_wal outside the data directories you must copy it
 > over to the new standby, and ensure the symlink from $PGDATA points to it"
 > helps?

 We can't document every possible configuration, especially if a
 secondary tool is used in the middle.
 
 
But we're not talking about many different configurations, we're addressing 
when pg_wal is located outside $PGDATA.
So it basically boils down to the last sentence in 10.F:
 
If you have relocated pg_wal outside the data directories, rsync must be run 
on those directories too.
 
the word "must" here isn't correct. The point is that you have to copy the 
waldir manually from the primary to the standby and ensure the symlink points 
to this new location on the standby. So I still think something like this is 
better: "If you have relocated pg_wal outside the data directories you must 
copy it over to the new standby, and ensure the symlink from 
$PGDATA/pg_wal points to it". I think it eliminates any doubt and makes the 
instructions complete and easy to follow.
 
Thanks.

 --
 Andreas Joseph Krogh


Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-16 Thread Andreas Joseph Krogh
På lørdag 16. september 2017 kl. 18:34:51, skrev Bruce Momjian <br...@momjian.us
 <mailto:br...@momjian.us>>:
On Sat, Sep 16, 2017 at 06:11:17PM +0200, Andreas Joseph Krogh wrote:
 > I'm a little unsure what scenario we're trying to describe here. Copying the
 > pg_wal separately (for which there's no need optimizing for) is only needed 
if
 > you've moved it out of $PGDATA _after_ running pg_upgrade, IIUC. So, I 
think it

 No.  If you ran initdb with --waldir on the new primary, you will create
 a symbolic link in the PGDATA directory, and a directory outside of
 PGDATA for storing the WAL.  When you run rsync on the new primary
 PGDATA directory, you will copy the symlink in the PGDATA directory, but
 it will point to probably nothing on the standby.
 
 
The misunderstanding here comes from the fact that I used pg_upgradecluster 
like this:
pg_upgradecluster --method=upgrade --link 9.6 main
  
and it didn't issue initdb with --waldir on the new cluster (because 
pg_upgradecluster issues initdb for you), so pg_wal ended up in $PGDIR because 
pg_upgradecluster didn't figure out the old cluster was initialized with 
--xlogdir. This is why I thought i made sense mentioning that one had to move 
pg_wal manually.
 
I know it's debian-stuff, but maybe it's worth mentioning pg_upgradecluster 
somewhere and recommend not using it? It seems to start the new cluster 
automatically and when upgrading standbys section 10 tells you not to do that.
 
 
> should be clearly pointed out that copying pg_wal is only needed in those
 > cases, and that it can be done with whatever network-copying procedure 
you're
 > familiar with, ie. scp/rsync. This step is not similar to the steps required
 > for copying tablespaces outside $PGDATA, so it's worth documenting 
explicitly.
 > Maybe also telling users to ensure the synlink (in $PGDATA) to pg_wal on
 > standby points to pg_wal.

 Why tell them new instructions when the rsync instructions work fine?
 What is the value?
 
The rsync instructions mentioned in 10.F all address the --link scenario and 
use "--delete --hard-links --size-only", and "merge 2 source-dirs into one", 
which isn't relevant when copying pg_wal.
 
This sentence:
"If you have relocated pg_wal outside the data directories, rsync must be run 
on those directories too."
implies one must follow the rsync pattern elsewhere in 10.F, which isn't 
really true. Maybe re-wording it to:
"If you have relocated pg_wal outside the data directories you must copy it 
over to the new standby, and ensure the symlink from $PGDATA points to it"
helps?

 --
 Andreas Joseph Krogh


Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-16 Thread Andreas Joseph Krogh
På lørdag 16. september 2017 kl. 17:24:14, skrev Bruce Momjian <br...@momjian.us
 <mailto:br...@momjian.us>>:
On Fri, Sep 15, 2017 at 01:23:45AM +0200, Andreas Joseph Krogh wrote:
 > I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade 
creates
 > the new data-dir with pg_wal "in it" (just like regular initdb), so 
pg_upgrade
 > seems not to care about where the old version's pg_xlog was. You have to 
move
 > (by symlinking) pg_wal to a separate location manually *after* running
 > pg_upgrade on the master. No special handling is needed when rsync'ing it 
over
 > to the standby, so it doesn't need any --hard-links or --size-only, correct?
 >  
 > Given the path, on the upgraded 
primary, to pg_wal is /custom/path/to/pg_wal,
 > the rsync command will be:
 >  
 > rsync --archive --delete /custom/path/to/pg_wal standby.example.com:/custom/
 > path/to/pg_wal
 >  
 > I think it's useful to mention this to eliminate any doubt.
 >  
 > I also think it's worth mentioning that you have to manually move pg_wal to 
a
 > custom location after running pg_upgrade as it will not preserve/use the old
 > path.

 Thinking some more, you are right that there is no need to rsync the
 _old_ primary WAL directory since it is the same on the standby old WAL
 directory, and there are no links between the old and new WAL
 directories, so you could just do the new one, or just copy it and not
 even use rsync.

 However, I think it adds complexity to try to optimize the copy of the
 WAL files and we are better just requiring them to use the same steps
 for WAL copy that they _must_ use for the data directory and
 tablespaces because of the links between old and new files there.

 Agreed?
 
I'm a little unsure what scenario we're trying to describe here. Copying the 
pg_wal separately (for which there's no need optimizing for) is only needed if 
you've moved it out of $PGDATA _after_ running pg_upgrade, IIUC. So, I think it 
should be clearly pointed out that copying pg_wal is only needed in those 
cases, and that it can be done with whatever network-copying procedure you're 
familiar with, ie. scp/rsync. This step is not similar to the steps required 
for copying tablespaces outside $PGDATA, so it's worth documenting explicitly. 
Maybe also telling users to ensure the synlink (in $PGDATA) to pg_wal on 
standby points to pg_wal.
 
--
 Andreas Joseph Krogh
 




Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-14 Thread Andreas Joseph Krogh
På torsdag 14. september 2017 kl. 21:13:56, skrev Bruce Momjian <
br...@momjian.us <mailto:br...@momjian.us>>:
On Thu, Sep 14, 2017 at 08:49:24PM +0200, Andreas Joseph Krogh wrote:
 >     I think the tablespace example is clear enough to modify for WAL and we
 >     instruct them right below that example to do WAL.
 >
 >  
 > Well, it's not following the exact same structure as there's no
 > "version-directory" in pg_xlog, so the "rsync the version-dirs into it's 
parent
 > on the target" isn't what's happening.
 >  
 > That's why I think this makes sense to mention for the sake of a complete
 > example:
 >
 > rsync --archive --delete --hard-links --size-only 
/vol1/postgres/9.6/pg_xlog \
 >       /vol1/postgres/10/pg_wal standby.example.com:/vol1/postgres/10/pg_wal

 Well, there is technically no need for version directories in pgdata
 either --- installers just create them.
 
 
I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade 
creates the new data-dir with pg_wal "in it" (just like regular initdb), so 
pg_upgrade seems not to care about where the old version's pg_xlog was. You 
have to move (by symlinking) pg_wal to a separate location manually *after* 
running pg_upgrade on the master. No special handling is needed when rsync'ing 
it over to the standby, so it doesn't need any --hard-links or --size-only, 
correct?
 
Given the path, on the upgraded primary, to pg_wal is /custom/path/to/pg_wal, 
the rsync command will be:
 
rsync --archive --delete /custom/path/to/pg_wal 
standby.example.com:/custom/path/to/pg_wal
 
I think it's useful to mention this to eliminate any doubt.
 
I also think it's worth mentioning that you have to manually move pg_wal to a 
custom location after running pg_upgrade as it will not preserve/use the old 
path.
 
Thanks.

 --
 Andreas Joseph Krogh


Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-14 Thread Andreas Joseph Krogh
På torsdag 14. september 2017 kl. 20:39:34, skrev Bruce Momjian <
br...@momjian.us <mailto:br...@momjian.us>>:
On Wed, Sep 13, 2017 at 04:31:09PM +0200, Andreas Joseph Krogh wrote:
 > På onsdag 13. september 2017 kl. 15:26:27, skrev Bruce Momjian <
 > br...@momjian.us>:
 >
 >     On Wed, Sep 13, 2017 at 01:35:17AM +0200, Andreas Joseph Krogh wrote:
 >     [snip]
 >     > I know I'm being a little nitty-gritty here, but if it helps me
 >     understand it
 >     > might help others.
 >
 >     I have applied the attached patch to show examples of using rsync on
 >     PGDATA and tablespaces, documented that rsync is only useful when in
 >     link mode, and explained more clearly how rsync handles links.  You can
 >     see the results here:
 >
 >     http://momjian.us/pgsql_docs/pgupgrade.html
 >
 >     Any more improvements?
 >
 >  
 > Very nice!
 >  
 > For sake of completeness I think an example of running rsync when 
having pg_wal
 > located outside the data directories would be helpful. Especially an example
 > upgrading from 9.6 to 10 because of the name-change of pg_xlog -> pg_wal.

 I think the tablespace example is clear enough to modify for WAL and we
 instruct them right below that example to do WAL.
 
Well, it's not following the exact same structure as there's no 
"version-directory" in pg_xlog, so the "rsync the version-dirs into it's parent 
on the target" isn't what's happening.
 
That's why I think this makes sense to mention for the sake of a complete 
example:
 rsync --archive --delete --hard-links --size-only /vol1/postgres/9.6/pg_xlog 
\ /vol1/postgres/10/pg_wal standby.example.com:/vol1/postgres/10/pg_wal 
 
Thanks.
 
--
 Andreas Joseph Krogh
 




Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-13 Thread Andreas Joseph Krogh
På onsdag 13. september 2017 kl. 15:26:27, skrev Bruce Momjian <br...@momjian.us
 <mailto:br...@momjian.us>>:
On Wed, Sep 13, 2017 at 01:35:17AM +0200, Andreas Joseph Krogh wrote:
 [snip]
 > I know I'm being a little nitty-gritty here, but if it helps me understand 
it
 > might help others.

 I have applied the attached patch to show examples of using rsync on
 PGDATA and tablespaces, documented that rsync is only useful when in
 link mode, and explained more clearly how rsync handles links.  You can
 see the results here:

 http://momjian.us/pgsql_docs/pgupgrade.html

 Any more improvements?
 
Very nice!
 
For sake of completeness I think an example of running rsync when 
having pg_wal located outside the data directories would be helpful. Especially 
an example upgrading from 9.6 to 10 because of the name-change of pg_xlog -> 
pg_wal.

 --
 Andreas Joseph Krogh


Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-12 Thread Andreas Joseph Krogh
På onsdag 13. september 2017 kl. 01:54:15, skrev Stephen Frost <
sfr...@snowman.net <mailto:sfr...@snowman.net>>:
Andreas,

 * Andreas Joseph Krogh (andr...@visena.com) wrote:
 > I have to ask; Why not run pg_upgrade on standby, after verifying that it's 
in
 > sync with primary and promoting it to primary if necessary and then making 
it
 > standby again after pg_upgrade is finished?

 I don't think that we could be guaranteed that the catalog tables would
 be the same on the replica as on the primary if they were actually
 created by pg_upgrade.

 The catalog tables *must* be identical between the primary and the
 replica because they are updated subsequently through WAL replay, not
 through SQL commands (which is how pg_upgrade creates them in the first
 place).

 Perhaps we could have some mode for pg_upgrade where it handles the
 update to replicas (with the additional checks that I outlined and using
 the methodology discussed for rsync --hard-links), but that would still
 require solving the communicate-over-the-network problem between the
 primary and the replicas, which is the hard part.  Whether it's an
 independent utility or something built into pg_upgrade isn't really that
 big of a distinction, though it doesn't seem to me like there'd be much
 code reuse there.

 Thanks!

 Stephen
 
Thanks.
 
--
 Andreas Joseph Krogh
 




Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-12 Thread Andreas Joseph Krogh
På onsdag 13. september 2017 kl. 01:38:40, skrev Stephen Frost <
sfr...@snowman.net <mailto:sfr...@snowman.net>>:
Bruce, all,
 [snip]

 Further, really, I think we should provide a utility to do all of the
 above instead of using rsync- and that utility should do some additional
 things, such as:

 - Check that the control file on the primary and replica show that they
   reached the same point prior to the pg_upgrade.  If they didn't, then
   things could go badly as there's unplayed WAL that the primary got
   through and the replica didn't.

 - Not copy over unlogged data, or any other information that shouldn't
   be copied across.

 - Allow the directory structures to be more different between the
   primary and the replica than rsync allows (wouldn't have to have a
   common subdirectory on the replica).

 - Perhaps other validation checks or similar.

 Unfortunately, this is a bit annoying as it necessairly involves running
 things on both the primary and the replica from the same tool, without
 access to PG, meaning we'd have to work through something else (such as
 SSH, like rsync does, but then what would we do for Windows...?).

 > > 3. What if the directory-layout isn't the same on primary and standby, ie.
 > > tablespaces are located differently?
 >
 > The way we reconfigured the location of tablespaces in PG 9.0 is that
 > each major version of Postgres places its tablespace in a subdirectory
 > of the tablespace directory, so there is tbldir/9.5 and tbldir/9.6.  If
 > your tbldir is different on the primary and standby, rsync will  still
 > work.  Everything _under_ the standby dir must be laid out the same, but
 > the directories above it can be different.

 That's correct, the directory to use for the tablespace actually *is*
 the tablespace directory (unlike the base directories, it doesn't need
 to be a directory above the tablespace directory, the documentation
 could probably be clearer on this point).

 As for all of the people raising concerns about if this process is
 correct or valid- I contend that the method used above, if done
 properly, isn't materially different from what pg_upgrade itself does.
 If we can't consider this safe then I'm not sure how we consider
 pg_upgrade safe.  (yes, I know there are some who don't, and that's a
 fair position to take also, but I consider the process above, when
 implemented correctly, is essentially the same).

 All that said, I honestly hadn't expected this method to end up in the
 documentation.  Not because I don't trust it or because I wanted to
 hoard the process, but because it takes a great deal of care and there's
 really additional validation that should be done (as discussed above)
 and those are things that I feel reasonable confident I'd remember to do
 when using such a procedure but which I wouldn't expect someone new to
 PG to realize they should do.

 Thanks!

 Stephen
 
 
Thanks for th explaination.
 
I have to ask; Why not run pg_upgrade on standby, after verifying that it's in 
sync with primary and promoting it to primary if necessary and then making it 
standby again after pg_upgrade is finished?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-12 Thread Andreas Joseph Krogh
På onsdag 13. september 2017 kl. 01:00:20, skrev Bruce Momjian <br...@momjian.us
 <mailto:br...@momjian.us>>:
On Wed, Sep 13, 2017 at 12:40:32AM +0200, Andreas Joseph Krogh wrote:
 > På tirsdag 12. september 2017 kl. 23:52:02, skrev Bruce Momjian <
 > br...@momjian.us>:
 >
 >     On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Joseph Krogh wrote:
 >     >     Improvements?
 >     >
 >     > Thanks, that certainly improves things.
 >     > But; I still find the rsync-command in f) confusing;
 >     > 1. Why --size-only? From rsync manual: "skip files that match in 
size",
 >     is this
 >     > safe??
 >
 >
 >     > 2. Why is old_pgdata in the rsync-command, why is it needed to sync 
it?
 >
 >     If the file exists under the same name, it doesn't need to be checked at
 >     all --- it is the same.  We don't want to check the file modification
 >     time because it will probably be different because of replay delay or
 >     clock drift.  We could use checksums, but there is no need since there 
is
 >     no way the file contents could be different.
 >
 >  
 >  
 > So you're saying that if the file exists (has the same name) on the standby 
(in
 > old_pgdata), and has the same size, then you're safe that it contains the 
same
 > data, hence --size-only?
 > Does this apply when not using --link mode for pg_upgrade?

 Well, it is really true in every case.  For link mode, we have to use an
 rsync command that lists both the old and new clusters on the command
 line (since we need rsync to see those hard links to reproduce them). If
 we don't use --size-only, we are going to checksum check the _old_ data
 cluster.  The new cluster will be empty so we will copy all of that (no
 need for a checksum there since there are no files).  I think you need
 size-only even without link since that old cluster is going to be listed
 for rsync.

 Now, what you could do, if you are _not_ using link mode, is to rsync
 only the new cluster, but the instructions we give work the same for
 link and non-link mode and produce the same results in the same time
 even if we had a non-link-mode example, so it seems we might as well
 just give one set of instructions.

 >     > There are many ways to do/configure things it seems, resulting in many
 >     ifs and
 >     > buts which makes section 10 rather confusing. I really think a 
complete
 >     > example, with absolute paths, would be clarifying.
 >
 >     You mean a full rsync command, e.g.:
 >
 >       rsync --archive --delete --hard-links --size-only \
 >           /opt/PostgreSQL/9.5 /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL
 >
 >     Does that help?
 >
 >  
 >  
 > It seems some non-obvious assumptions (to me at least) are made here.
 > This example seems only valid when using pg_upgrade --link, correct? If so 
it
 > would be clearer to the reader if explicitly stated.

 Well, as I stated above, --hard-links is only going to recreate hard
 links on the standby that exist on the primary, and if you didn't use
 pg_upgrade's --link mode, there will be none, so it is harmless if
 pg_upgrade --link mode was not used.

 > 1. Why do you have to rsync both /opt/PostgreSQL/9.5 AND 
/opt/PostgreSQL/9.6,
 > wouldn't /opt/PostgreSQL/9.6 suffice? Or does this assume "pg_upgrade 
--link"
 > AND "rsync --hard-links" and therefore it somewhat needs to transfer less 
data?

 As I stated above, rsync has to see _both_ hard links on the primary to
 recreate them on the standby.  I thought the doc patch was clear on
 that, but obviously not.  :-(  Suggestions?  (Yes, I admit that using
 rsync in this way is super-crafty, and I would _love_ to take credit for
 the idea, but I think the award goes to Stephen Frost.)

 > 2. What would the rsync command look like if pg_upgrade wasn't issued with
 > --link?

 It would look like:

   rsync --archive /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL/9.6

 but effectively there isn't anything _in_ standby:/opt/PostgreSQL/9.6,
 so you are really just using rsync as cp, and frankly I have found 'cp'
 is faster than rsync when nothing exists on the other side so it really
 becomes "just copy the cluster when the server is down", but I don't
 think people even need instructions for that.

 Maybe we should recommend rsync only for pg_upgrade --link mode?

 > 3. What if the directory-layout isn't the same on primary and standby, ie.
 > tablespaces are located differently?

 The way we reconfigured the location of tablespaces in PG 9.0 is that
 each major version of Postgres places its tablespace in a subdirectory
 of the tablespace directory, so there is tbldir/9.5 and tbldir/9.6.  If
 your tbldir is different on the primary and standby, rsync will  still
 work.  Everything _under_ the standby dir must be laid out the sa

Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-12 Thread Andreas Joseph Krogh
På tirsdag 12. september 2017 kl. 23:52:02, skrev Bruce Momjian <
br...@momjian.us <mailto:br...@momjian.us>>:
On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Joseph Krogh wrote:
 >     Improvements?
 >
 > Thanks, that certainly improves things.
 > But; I still find the rsync-command in f) confusing;
 > 1. Why --size-only? From rsync manual: "skip files that match in size", is 
this
 > safe??


 > 2. Why is old_pgdata in the rsync-command, why is it needed to sync it?

 If the file exists under the same name, it doesn't need to be checked at
 all --- it is the same.  We don't want to check the file modification
 time because it will probably be different because of replay delay or
 clock drift.  We could use checksums, but there is no need since there is
 no way the file contents could be different.
 
 
So you're saying that if the file exists (has the same name) on the standby 
(in old_pgdata), and has the same size, then you're safe that it contains the 
same data, hence --size-only?
Does this apply when not using --link mode for pg_upgrade?
 
 
> There are many ways to do/configure things it seems, resulting in many ifs 
and
 > buts which makes section 10 rather confusing. I really think a complete
 > example, with absolute paths, would be clarifying.

 You mean a full rsync command, e.g.:

   rsync --archive --delete --hard-links --size-only \
       /opt/PostgreSQL/9.5 /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL

 Does that help?
 
 
It seems some non-obvious assumptions (to me at least) are made here.
This example seems only valid when using pg_upgrade --link, correct? If so it 
would be clearer to the reader if explicitly stated.
 
1. Why do you have to rsync both /opt/PostgreSQL/9.5 AND /opt/PostgreSQL/9.6, 
wouldn't /opt/PostgreSQL/9.6 suffice? Or does this assume "pg_upgrade --link" 
AND "rsync --hard-links" and therefore it somewhat needs to transfer less data?
2. What would the rsync command look like if pg_upgrade wasn't issued with 
--link?
3. What if the directory-layout isn't the same on primary and standby, ie. 
tablespaces are located differently?
 
Thanks.
 
--
 Andreas Joseph Krogh
 




Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-12 Thread Andreas Joseph Krogh
På tirsdag 12. september 2017 kl. 21:11:45, skrev Robert Haas <
robertmh...@gmail.com <mailto:robertmh...@gmail.com>>:
On Tue, Sep 12, 2017 at 2:59 PM, Andreas Joseph Krogh
 <andr...@visena.com> wrote:
 > There are many ways to do/configure things it seems, resulting in many ifs
 > and buts which makes section 10 rather confusing. I really think a complete
 > example, with absolute paths, would be clarifying.
 >
 > I'm afraid many will still re-create standbys from scratch without a really
 > good and complete example to follow.

 And I'm afraid that they won't.
 
Yea. Put it that way - me too:-)
The consequences of not re-creating standbys from scratch and not 
understanding section 10, and doing it wrong, are far worse...
 
--
 Andreas Joseph Krogh
 




Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-09-12 Thread Andreas Joseph Krogh
På tirsdag 12. september 2017 kl. 19:19:22, skrev Bruce Momjian <
br...@momjian.us <mailto:br...@momjian.us>>:
On Thu, Aug  3, 2017 at 11:37:32AM +0200, Michael Paquier wrote:
 > On Mon, Jul 31, 2017 at 6:13 PM, Robert Haas <robertmh...@gmail.com> wrote:
 > > On Fri, Jul 28, 2017 at 10:35 AM, Andreas Joseph Krogh
 > > <andr...@visena.com> wrote:
 > >> I'm reading https://www.postgresql.org/docs/10/static/pgupgrade.html to 
try
 > >> to understand how to upgrade standby-servers using pg_upgrade with pg10.
 > >>
 > >> The text in step 10 sais:
 > >> "You will not be running pg_upgrade on the standby servers, but rather
 > >> rsync", which to me sounds like rsync, in step 10-f, should be issued on 
the
 > >> standy servers. Is this the case? If so I don't understand how the 
standby's
 > >> data is upgraded and what "remote_dir" is. If rsync is supposed to be 
issued
 > >> on the primary then I think it should be explicitly mentioned, and step 
10-f
 > >> should provide a clarer example with more detailed values for the
 > >> directory-structures involved.
 > >>
 > >> I really think section 10 needs improvement as I'm certainly not 
comfortable
 > >> upgrading standbys following the existing procedure.
 > >
 > > Yeah, I don't understand it either, and I have never been convinced
 > > that there's any safe way to do it other than recloning the standbys
 > > from the upgraded master.
 >
 > Here are my 2c on the matter. 10-f means that the upgraded node may
 > have generated WAL with wal_level = minimal, which, at least it seems
 > to me, that we have a risk of having inconsistent data pages if only a
 > rsync is used on the old standbys. Like Robert, the flow we used in
 > the products I work on is to re-create standbys from scratch after the
 > upgrade using a fresh backup, with a VM cloning. An upgrade here is an
 > in-place process not only linked to Postgres, so standby VMs are made
 > of many services, some are being linked to Postgres. So this choice is
 > mainly decided by those dependencies, still it feels safer anyway.

 I have applied the attached doc patch back to 9.5 to clarify
 pg_upgrade's rsync instructions and explain how it works.

 Improvements?
 
 
Thanks, that certainly improves things.
But; I still find the rsync-command in f) confusing;
1. Why --size-only? From rsync manual: "skip files that match in size", is 
this safe??
2. Why is old_pgdata in the rsync-command, why is it needed to sync it?
 
There are many ways to do/configure things it seems, resulting in many ifs and 
buts which makes section 10 rather confusing. I really think a complete 
example, with absolute paths, would be clarifying.
 
I'm afraid many will still re-create standbys from scratch without a really 
good and complete example to follow.

 --
 Andreas Joseph Krogh


Re: [HACKERS] Release Note changes

2017-09-04 Thread Andreas Joseph Krogh
På mandag 04. september 2017 kl. 10:49:40, skrev Simon Riggs <
si...@2ndquadrant.com <mailto:si...@2ndquadrant.com>>:
Migration to Version 10

 "A dump/restore using pg_dumpall, or use of pg_upgrade, is required
 for those wishing to migrate data from any previous release."

 This isn't true and is seriously misleading since pglogical and other
 proprietary tools exist that were designed specifically to allow this.
 Suggested additional wording would be...

 "If upgrading from a 9.4 server or later, external utilities using
 logical decoding, such as pglogical or proprietary alternatives, can
 also provide an alternate route, often with lower downtime."

 Our docs mention pglogical already, so don't see an issue with
 mentioning it here.
 
I'd like at big red warning "Logical decoding doesn't support Large Objects" 
in that case;
 
"If upgrading from a 9.4 server or later, and you don't use Large Objects,
external utilities using logical decoding, such as pglogical or
proprietary alternatives, can also provide an alternate route,
often with lower downtime."
 
pg_upgrade or pg_dump is really the only option for us using LOs.
 
-- Andreas Joseph Krogh




[HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)

2017-07-28 Thread Andreas Joseph Krogh
Hi -hackers.
 
I'm reading https://www.postgresql.org/docs/10/static/pgupgrade.html to try to 
understand how to upgrade standby-servers using pg_upgrade with pg10.
 
The text in step 10 sais:
"You will not be running pg_upgrade on the standby servers, but rather rsync", 
which to me sounds like rsync, in step 10-f, should be issued on the standy 
servers. Is this the case? If so I don't understand how the standby's data is 
upgraded and what "remote_dir" is. If rsync is supposed to be issued on the 
primary then I think it should be explicitly mentioned, and step 10-f should 
provide a clarer example with more detailed values for the directory-structures 
involved.
 
I really think section 10 needs improvement as I'm certainly not comfortable 
upgrading standbys following the existing procedure.
 
Thanks.
 
--
Andreas Joseph Krogh


Re: [HACKERS] CTE inlining

2017-05-12 Thread Andreas Joseph Krogh
På torsdag 11. mai 2017 kl. 23:37:27, skrev Yaroslav <ladayaros...@yandex.ru 
<mailto:ladayaros...@yandex.ru>>:
Ilya Shkuratov wrote
 > First of all, to such replacement to be valid, the CTE must be
 >     1. non-writable (e.g. be of form: SELECT ...),
 >     2. do not use VOLATILE or STABLE functions,
 >     3. ... (maybe there must be more restrictions?)

 What about simple things like this?

 CREATE OR REPLACE FUNCTION z(numeric) RETURNS boolean AS $$
 BEGIN
 RETURN $1 <> 0;
 END;
 $$ LANGUAGE plpgSQL IMMUTABLE COST 1000;

 -- This one works:
 WITH T AS (
 SELECT 1.0 AS v1, 0.0 AS v2
 UNION ALL
 SELECT 3.0, 1.0
 UNION ALL
 SELECT 2.0, 0.0
 ), a AS (
 SELECT *
   FROM t
  WHERE z(v2)
 )
 SELECT *
   FROM a
  WHERE v1/v2 > 1.5;

 -- This one gives 'division by zero':
 WITH T AS (
 SELECT 1.0 AS v1, 0.0 AS v2
 UNION ALL
 SELECT 3.0, 1.0
 UNION ALL
 SELECT 2.0, 0.0
 )
 SELECT *
   FROM (
        SELECT *
          FROM t
         WHERE z(v2)
        ) AS a
  WHERE v1/v2 > 1.5;
  
 
>From a non-hacker;
Just to se what other RDBMS are doing with CTEs; Look at slide 
31 here: 
https://www.percona.com/live/17/sites/default/files/slides/Recursive%20Query%20Throwdown.pdf
 
PG is not on top wrt. CTE, but could have been if CTEs were not this 
"established" fence.
 
+1 for removing this fence and get all the possible optimization we can.

 --
 Andreas Joseph Krogh
 


Re: [HACKERS] Gather Merge

2017-03-10 Thread Andreas Joseph Krogh
På fredag 10. mars 2017 kl. 10:34:48, skrev Rushabh Lathia <
rushabh.lat...@gmail.com <mailto:rushabh.lat...@gmail.com>>:
    On Fri, Mar 10, 2017 at 2:42 PM, Andreas Joseph Krogh <andr...@visena.com 
<mailto:andr...@visena.com>> wrote: På fredag 10. mars 2017 kl. 10:09:22, skrev 
Rushabh Lathia <rushabh.lat...@gmail.com <mailto:rushabh.lat...@gmail.com>>:
    On Fri, Mar 10, 2017 at 2:33 PM, Andreas Joseph Krogh <andr...@visena.com 
<mailto:andr...@visena.com>> wrote: [...]


The execution-plan seems (unsurprisingly) to depend on data-distribution, so 
is there a way I can force a GatherMerge?
 
Not directly. GatherMerge cost is mainly depend on parallel_setup_cost,
parallel_tuple_cost and cpu_operator_cost. May be you can force this
by setting this cost low enough. Or another way to force is by disable the
other plans.
 
What plan you are getting now? You not seeing the below error ?
 
ERROR:  GatherMerge child's targetlist doesn't match GatherMerge



 
I'm seeing the same error, it's just that for reproducing it I'd rather not 
copy my whole dataset.
 
Can you share me a schema information, I will try to reproduce at my side?



 
The relevant schema is this:
 
drop table if EXISTS temp_email_address_owner; drop table if EXISTS 
temp_email_delivery;drop table if EXISTS temp_email_message; create table 
temp_email_message( entity_idBIGSERIAL PRIMARY KEY ); create table 
temp_email_delivery( entity_idBIGSERIAL PRIMARY KEY, message_id bigint not null 
referencestemp_email_message(entity_id), from_entity_id bigint, 
received_timestamptimestamp not null ); create table temp_email_address_owner( 
entity_idBIGSERIAL PRIMARY KEY, message_id bigint not null references 
temp_email_message(entity_id), recipient_idbigint ); 
EXPLAIN ANALYSE SELECT em.entity_id FROM temp_email_delivery del JOIN 
temp_email_message emON (del.message_id = em.entity_id) WHERE 
del.from_entity_id =279519 OR em.entity_id IN ( SELECT ea_owner.message_id FROM 
temp_email_address_owner ea_ownerWHERE ea_owner.recipient_id = 279519 ) ORDER BY
del.received_timestampDESC  LIMIT 101 OFFSET 0; 
 
.. But I'm having a hard time reproducing it.
I've tried to copy data from the relevant tables to the test-tables (temp_*), 
adding indexes etc. but Gathre Merge works just fine:
 
│ Limit  (cost=209378.96..209391.05 rows=101 width=16) (actual 
time=799.380..799.432 rows=101 loops=1) 

  │
 │   ->  Gather Merge  (cost=209378.96..262335.79 rows=442285 width=16) 
(actual time=799.379..799.420 rows=101 loops=1) 

  │
 │ Workers Planned: 4 


│
 │ Workers Launched: 4 


   │
 │ ->  Sort  (cost=208378.90..208655.33 rows=110571 width=16) (actual 
time=785.029..785.042 rows=81 loops=5) 

 │
 │   Sort Key: del.received_timestamp DESC 

   │
 │   Sort Method: quicksort  Memory: 29kB 

│
 │   ->  Hash Join  (cost=52036.86..204145.01 rows=110571 
width=16) (actual time=400.812..784.907 rows=95 loops=5) 
   │
 │ Hash Cond: (del.message_id = em.entity_id) 

│
 │ Join Filter: ((del.from_entity_id = 279519) OR (hashed 
SubPlan 1)) 

│
 │ Rows Removed by Join Filter: 176799 

   │
 │ ->  Parallel Seq Scan on temp_email_delivery del 
 (cost=0.00..142515.18 rows=221118 width=24) (actual time=0.033..211.196 
rows=176894 loops=5) │
 │ ->  Hash  (cost=39799.72..39799.72 rows=730

Re: [HACKERS] Gather Merge

2017-03-10 Thread Andreas Joseph Krogh
På fredag 10. mars 2017 kl. 10:09:22, skrev Rushabh Lathia <
rushabh.lat...@gmail.com <mailto:rushabh.lat...@gmail.com>>:
    On Fri, Mar 10, 2017 at 2:33 PM, Andreas Joseph Krogh <andr...@visena.com 
<mailto:andr...@visena.com>> wrote: [...]


The execution-plan seems (unsurprisingly) to depend on data-distribution, so 
is there a way I can force a GatherMerge?
 
Not directly. GatherMerge cost is mainly depend on parallel_setup_cost,
parallel_tuple_cost and cpu_operator_cost. May be you can force this
by setting this cost low enough. Or another way to force is by disable the
other plans.
 
What plan you are getting now? You not seeing the below error ?
 
ERROR:  GatherMerge child's targetlist doesn't match GatherMerge



 
I'm seeing the same error, it's just that for reproducing it I'd rather not 
copy my whole dataset.
 
-- Andreas Joseph Krogh




Re: [HACKERS] Gather Merge

2017-03-10 Thread Andreas Joseph Krogh
På fredag 10. mars 2017 kl. 09:53:47, skrev Rushabh Lathia <
rushabh.lat...@gmail.com <mailto:rushabh.lat...@gmail.com>>:
    On Fri, Mar 10, 2017 at 1:44 PM, Andreas Joseph Krogh <andr...@visena.com 
<mailto:andr...@visena.com>> wrote: På torsdag 09. mars 2017 kl. 18:09:45, 
skrev Robert Haas <robertmh...@gmail.com <mailto:robertmh...@gmail.com>>:
On Thu, Mar 9, 2017 at 11:25 AM, Rushabh Lathia
 <rushabh.lat...@gmail.com <mailto:rushabh.lat...@gmail.com>> wrote:
 > I don't see this failure with the patch. Even I forced the gather merge
 > in the above query and that just working fine.
 >
 > Attaching patch, with the discussed changes.

 Committed.
 
 
I'm still getting (as of 9c2635e26f6f4e34b3b606c0fc79d0e111953a74): 
ERROR:  GatherMerge child's targetlist doesn't match GatherMerge

  
from this query:
 
EXPLAIN ANALYSE SELECT em.entity_id FROM origo_email_delivery del JOIN 
origo_email_message emON (del.message_id = em.entity_id) WHERE 1 = 1 AND 
del.owner_id =3 AND ( del.from_entity_id = 279519 OR del.from_entity_id = 3 AND 
em.entity_idIN ( SELECT ea_owner.message_id FROM origo_email_address_owner 
ea_ownerWHERE ea_owner.recipient_id = 279519 ) ) ORDER BY del.received_timestamp
DESCLIMIT 101 OFFSET 0; 
 
Is this known or shall I provide more info/schema etc?
 
Please provide the reproducible test if possible.



 
The execution-plan seems (unsurprisingly) to depend on data-distribution, so 
is there a way I can force a GatherMerge?
 
-- Andreas Joseph Krogh




Re: [HACKERS] Gather Merge

2017-03-10 Thread Andreas Joseph Krogh
 │
 │ Planning time: 1.372 ms 


 │
 │ Execution time: 170.859 ms 

        
  │

  
 
-- Andreas Joseph Krogh
 




[HACKERS] Error building docs

2017-01-20 Thread Andreas Joseph Krogh
Hi,
 
I'm getting this error building docs (from commit 
e4c27f5befbfc80a1bf96fc93256dce08b148238):
 
$ make docs  
 make -C doc all
 make[1]: Entering directory '/home/andreak/dev/postgresql/doc'
 make -C src all
 make[2]: Entering directory '/home/andreak/dev/postgresql/doc/src'
 make -C sgml all
 make[3]: Entering directory '/home/andreak/dev/postgresql/doc/src/sgml'
 { \
  echo ""; \
  echo ""; \
 } > version.sgml
 '/usr/bin/perl' ./mk_feature_tables.pl YES 
../../../src/backend/catalog/sql_feature_packages.txt 
../../../src/backend/catalog/sql_features.txt > features-supported.sgml
 '/usr/bin/perl' ./mk_feature_tables.pl NO 
../../../src/backend/catalog/sql_feature_packages.txt 
../../../src/backend/catalog/sql_features.txt > features-unsupported.sgml
 '/usr/bin/perl' ./generate-errcodes-table.pl 
../../../src/backend/utils/errcodes.txt > errcodes-table.sgml
 osx -D. -x lower -i include-xslt-index postgres.sgml >postgres.xmltmp
 osx:postgres.sgml:3:55:W: cannot generate system identifier for public text 
"-//OASIS//DTD DocBook V4.2//EN"
 osx:postgres.sgml:12:0:E: reference to entity "BOOK" for which no system 
identifier could be generated
 osx:postgres.sgml:3:0: entity was defined here
 osx:postgres.sgml:12:0:E: DTD did not contain element declaration for 
document type name
 osx:postgres.sgml:14:9:E: there is no attribute "ID"
 osx:postgres.sgml:14:19:E: element "BOOK" undefined
 osx:postgres.sgml:15:7:E: element "TITLE" undefined
 osx:postgres.sgml:17:10:E: element "BOOKINFO" undefined
 osx:postgres.sgml:18:13:E: element "CORPAUTHOR" undefined
 osx:postgres.sgml:19:14:E: element "PRODUCTNAME" undefined
 osx:postgres.sgml:20:16:E: element "PRODUCTNUMBER" undefined

  
$ osx --version 
 osx:I: "OpenSP" version "1.5.2"

 Any hints?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>



Re: [HACKERS] Move pg_largeobject to a different tablespace *without* turning on system_table_mods.

2016-10-19 Thread Andreas Joseph Krogh
På onsdag 19. oktober 2016 kl. 18:44:24, skrev Tom Lane <t...@sss.pgh.pa.us 
<mailto:t...@sss.pgh.pa.us>>:
Andreas Joseph Krogh <andr...@visena.com> writes:
 > På onsdag 19. oktober 2016 kl. 18:29:31, skrev Bruce Momjian 
<br...@momjian.us
 >  I think an open question is why you would not want to move the other
 >  system tables at the same time you move pg_largeobject.

 > Are you saying that if I move all system-tables to the tablespace I moved
 > pg_largeobject to it'll work? If so, is there a convenient way to move all
 > system-tables to a tablespace?

 Not sure about moving them after the fact, but you could create the
 database with its default tablespace being the one you want pg_largeobject
 in.

 I think though that there's a fairly clear counterexample to Bruce's
 question: if you're worried about moving pg_largeobject at all, you
 probably are trying to put it on a relatively large and slow storage
 device.  You don't necessarily want all the system catalogs there.

 regards, tom lane
 
Thanks for the tip. How do I conveniently move all the 
tables/indexes/sequences etc. (basically everything in schema=public) except 
the system-tables to another tablespace?
I don't see any "ALTER SCHEMA public SET TABLESPACE myspace" command...
 
This is great when dealing with new databases, but do you have any hints 
helping me out getting pg_upgrade working now that I already have moved 
pg_largeobject (see my answer to Bruce)?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


Re: [HACKERS] Move pg_largeobject to a different tablespace *without* turning on system_table_mods.

2016-10-19 Thread Andreas Joseph Krogh
På onsdag 19. oktober 2016 kl. 18:42:11, skrev Bruce Momjian <br...@momjian.us 
<mailto:br...@momjian.us>>:
On Wed, Oct 19, 2016 at 06:33:55PM +0200, Andreas Joseph Krogh wrote:
 > På onsdag 19. oktober 2016 kl. 18:29:31, skrev Bruce Momjian 
<br...@momjian.us
 > >:
 >
 >     On Tue, Oct 18, 2016 at 04:51:54PM +0200, Andreas Joseph Krogh wrote:
 >     >     > 2. Being able to move pg_largeobject to a different tablespace
 >     >     >    *without* turning on system_table_mods. This is important for
 >     >     >    people storing LOTS of large-objects on separate
 >     >     >    disks (non-SSD) and hence in a different tablespace.
 >     >     > Anyone willing to discuss this?
 >     >     > 
 >     >     This was proposed a few years ago but no one cared to draft a 
patch.
 >     >
 >     >  
 >     > So that why I'm re-raising the issue:-)
 >     > Having "everything in the database" adds lots of benefits, 
conceptually
 >     > (follows tx-semantics, consistent backups etc.), however it's 
currently
 >     not so
 >     > easy in practice.
 >
 >     Yeah, rereading that old thread was interesting, and unfortunate that no
 >     one mentioned the system catalog change would break pg_upgrade, though
 >     pg_upgrade was not popular at the time that thread was started.
 >
 >     I think an open question is why you would not want to move the other
 >     system tables at the same time you move pg_largeobject.
 >
 >  
 > The thing is that I don't understand what the problem really is. I have no
 > problem moving the other system-tables as well if that fixes the problem.
 > I tried moving pg_largeobject back to the same tablespace as the database 
but
 > that too gave the error.
 >  
 > Are you saying that if I move all system-tables to the tablespace I moved
 > pg_largeobject to it'll work? If so, is there a convenient way to move all
 > system-tables to a tablespace?

 Sure, use:

       ALTER DATABASE name SET TABLESPACE new_tablespace

       ...

       The fourth form changes the default tablespace of the database. Only
       the database owner or a superuser can do this; you must also have
       create privilege for the new tablespace. This command physically
       moves any tables or indexes in the database's old default tablespace
       to the new tablespace. The new default tablespace must be empty for
       this database, and no one can be connected to the database. Tables
       and indexes in non-default tablespaces are unaffected.
 
The thing is; I've created the database with explicit tablespace, like this:
createdb --tablespace=mydb -O andreak mydb
 
Then I've moved pg_largeobject:
 
alter table pg_largeobject set tablespace mydb_lo
 
What options do I now have to make pg_upgrade work? I have 6TB db which I'd 
like to upgrade to 9.6 using pg_upgrade so any help accomplishing that is 
greatly appreciated:-)
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


Re: [HACKERS] Move pg_largeobject to a different tablespace *without* turning on system_table_mods.

2016-10-19 Thread Andreas Joseph Krogh
På onsdag 19. oktober 2016 kl. 18:29:31, skrev Bruce Momjian <br...@momjian.us 
<mailto:br...@momjian.us>>:
On Tue, Oct 18, 2016 at 04:51:54PM +0200, Andreas Joseph Krogh wrote:
 >     > 2. Being able to move pg_largeobject to a different tablespace
 >     >    *without* turning on system_table_mods. This is important for
 >     >    people storing LOTS of large-objects on separate
 >     >    disks (non-SSD) and hence in a different tablespace.
 >     > Anyone willing to discuss this?
 >     > 
 >     This was proposed a few years ago but no one cared to draft a patch.
 >
 >  
 > So that why I'm re-raising the issue:-)
 > Having "everything in the database" adds lots of benefits, conceptually
 > (follows tx-semantics, consistent backups etc.), however it's currently not 
so
 > easy in practice.

 Yeah, rereading that old thread was interesting, and unfortunate that no
 one mentioned the system catalog change would break pg_upgrade, though
 pg_upgrade was not popular at the time that thread was started.

 I think an open question is why you would not want to move the other
 system tables at the same time you move pg_largeobject.
 
The thing is that I don't understand what the problem really is. I have no 
problem moving the other system-tables as well if that fixes the problem.
I tried moving pg_largeobject back to the same tablespace as the database but 
that too gave the error.
 
Are you saying that if I move all system-tables to the tablespace I moved 
pg_largeobject to it'll work? If so, is there a convenient way to move all 
system-tables to a tablespace?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


Re: [HACKERS] Move pg_largeobject to a different tablespace *without* turning on system_table_mods.

2016-10-18 Thread Andreas Joseph Krogh
På tirsdag 18. oktober 2016 kl. 16:26:37, skrev Euler Taveira <
eu...@timbira.com.br <mailto:eu...@timbira.com.br>>:
On 18-10-2016 10:13, Andreas Joseph Krogh wrote:
 > From time to time pg_largeobject comes up as an issue with being
 > implemented as a system-catalog.
 > 
 Did you read the archives [1]?
 
 
Yes..
 
> As I see it, there are 2 relevant use-cases for improving the situation:
 > 1. Being able to pg_dump *without* any LOs (think of it as
 >    without the contents of pg_largeobject). This is very handy
 >    for testing/troubleshooting.
 >
 It could be an option (--no-blobs). The -b option has a limited use case.
 
 
Yes, it definitely should be an option to pg_dump. I guess because of 
pg_largeobject being a system-catalog it adds additional difficulties 
implementing it?
 
> 2. Being able to move pg_largeobject to a different tablespace
 >    *without* turning on system_table_mods. This is important for
 >    people storing LOTS of large-objects on separate
 >    disks (non-SSD) and hence in a different tablespace.
 > Anyone willing to discuss this?
 > 
 This was proposed a few years ago but no one cared to draft a patch.
 
So that why I'm re-raising the issue:-)
Having "everything in the database" adds lots of benefits, conceptually 
(follows tx-semantics, consistent backups etc.), however it's currently not so 
easy in practice.
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


[HACKERS] Move pg_largeobject to a different tablespace *without* turning on system_table_mods.

2016-10-18 Thread Andreas Joseph Krogh
Hi -hackers.
 
>From time to time pg_largeobject comes up as an issue with being implemented 
as a system-catalog.
 
As I see it, there are 2 relevant use-cases for improving the situation:
1. Being able to pg_dump *without* any LOs (think of it as
    without the contents of pg_largeobject). This is very handy
    for testing/troubleshooting.
2. Being able to move pg_largeobject to a different tablespace
    *without* turning on system_table_mods. This is important for
    people storing LOTS of large-objects on separate
    disks (non-SSD) and hence in a different tablespace.

Anyone willing to discuss this?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>




Re: [HACKERS] pg_upgrade 9.5 -> 9.6 fails when pg_largeobject is in separate tablespace

2016-10-10 Thread Andreas Joseph Krogh
På søndag 09. oktober 2016 kl. 23:43:23, skrev Robert Haas <
robertmh...@gmail.com <mailto:robertmh...@gmail.com>>:
On Sat, Oct 8, 2016 at 9:02 AM, Andreas Joseph Krogh <andr...@visena.com 
<mailto:andr...@visena.com>> wrote: (I've set allow_system_table_mods=on in 
postgresql.conf)


Any configuration that includes this step is considered unsupported by the 
PostgreSQL community.
  
It might be a good idea if we supported storing large objects in an alternate 
tablespace, or in multiple tables in the same or different tablespaces.  
However, if you can only get there by enabling allow_system_table_mods, then we 
don't.

 
Note that pg_largeobject can be moved without 
changing allow_system_table_mods, namely by starting in single-user-mode, so I 
really don't se why this is considered unsupported? I would assume that having 
pg_largeobject in a separate tablespace is more and more common these days, 
having real-cheap SAN vs. fast-SSD for normal tables/indexes/wal.
 
AFAICT the very existence of pg_largeobject is an implementation-detail(and it 
being a system-catalog considered a defect) so saying that by moving it 
you're not able to use tools like pg_upgrade feels like being left out in the 
cold...
 
Is fixing this in any plans? Is this something we can pay for getting fixed, 
if so - what would it take?
 
PS: I cannot see this shortcoming being documented anywhere in pg_upgrade's 
docs ( https://www.postgresql.org/docs/9.6/static/pgupgrade.html ), is it 
mentioned anywhere?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


[HACKERS] pg_upgrade 9.5 -> 9.6 fails when pg_largeobject is in separate tablespace

2016-10-08 Thread Andreas Joseph Krogh
Hi.
 
(if this is not the right forum, please point me to it)
 
I have an issue with pg_upgrade upgrading 9.5 to 9.6. (my system is 
Ubuntu-16.04 and packages from http://apt.postgresql.org/)
 
In short pg_upgrade fails with:
 
Linking user relation files
 No match found in new cluster for old relation with OID 16388 in database 
"andreak": "pg_toast.pg_toast_2613" which is the TOAST table for 
"pg_catalog.pg_largeobject"
 No match found in new cluster for old relation with OID 16390 in database 
"andreak": "pg_toast.pg_toast_2613_index" which is an index on 
"pg_toast.pg_toast_2613" which is the TOAST table for 
"pg_catalog.pg_largeobject"
Failed to match up old and new tables in database "andreak"
 Failure, exiting
 
I issued the following command:
PG_NEW_VERSION=9.6
 PG_OLD_VERSION=9.5
 /usr/lib/postgresql/$PG_NEW_VERSION/bin/pg_upgrade \
           --old-bindir=/usr/lib/postgresql/$PG_OLD_VERSION/bin/ \
           --new-bindir=/usr/lib/postgresql/$PG_NEW_VERSION/bin/ \
           --old-datadir=/var/lib/postgresql/$PG_OLD_VERSION/main \
           --new-datadir=/var/lib/postgresql/$PG_NEW_VERSION/main \
           -o " -c 
config_file=/etc/postgresql/$PG_OLD_VERSION/main/postgresql.conf" \
           -O " -c 
config_file=/etc/postgresql/$PG_NEW_VERSION/main/postgresql.conf" \
           --link

 
I have pg_largeobject in a separate tablespace, moved by the following command:
psql -U postgres -c "alter table pg_largeobject set tablespace andreak_lo" -d 
andreak
 
 
(I've set allow_system_table_mods=on in postgresql.conf)
 
Else I have tablespaces configured outside PGDATA:
mkdir /var/lib/postgresql/9.5/tablespaces/andreak
 mkdir /var/lib/postgresql/9.5/tablespaces_lo/andreak
 
These are symlinked:
ln -s /storage/wal/9.5/pg_xlog /var/lib/postgresql/9.5/main/pg_xlog
 ln -s /storage/fast_ssd/9.5/tablespaces /var/lib/postgresql/9.5/tablespaces
 ln -s /storage/fast_ssd/9.5/tablespaces_lo 
/var/lib/postgresql/9.5/tablespaces_lo
 
 
psql -c "create tablespace andreak OWNER andreak location 
'/var/lib/postgresql/9.5/tablespaces/andreak'" postgres;
 psql -c "create tablespace andreak_lo OWNER andreak location 
'/var/lib/postgresql/9.5/tablespaces_lo/andreak'" postgres;
 createdb --tablespace=andreak -O andreak andreak
psql -U postgres -c "alter table pg_largeobject set tablespace andreak_lo" -d 
andreak

Is this a bug or not a supported configuration by pg_upgraded?
Any hints on how to proceed?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>




Re: [HACKERS] Bogus ANALYZE results for an otherwise-unique column with many nulls

2016-08-14 Thread Andreas Joseph Krogh
Join (cost=1069.29..7964.73 rows=84494 width=4) (actual time
=15.605..102.721rows=85668 loops=1) Hash Cond: (il.invoice_id = inv.entity_id) 
->Hash Anti Join (cost=536.55..6265.54 rows=85741 width=8) (actual time
=5.467..71.124rows=88731 loops=1) Hash Cond: (il.invoice_id = 
creditnote.credit_against) -> MergeJoin (cost=0.61..4501.71 rows=89266 width=8) 
(actualtime=0.441..53.014 rows=89301 loops=1) Merge Cond: (il.id = 
cem.invoice_line_id) ->Index Only Scan using origo_invoice_line_id_invoice_idx 
ononp_crm_invoice_line il (cost=0.29..1530.95 rows=78707 width=8) (actual time
=0.226..15.901rows=78787 loops=1) Heap Fetches: 3131 -> Index Only Scan using 
onp_crm_calendarentry_invoice_invoice_line_id_calendar_entr_keyon 
onp_crm_calendarentry_invoice_membership cem (cost=0.29..1662.98 rows=89266 
width=8) (actual time=0.194..16.489 rows=89301 loops=1) Heap Fetches: 5027 -> 
Hash(cost=390.36..390.36 rows=11647 width=8) (actual time=4.785..4.785 rows=372 
loops=1) Buckets: 16384 Batches: 1 Memory Usage: 143kB -> Index Only Scan using 
origo_invoice_credit_against_status_sent_idxon onp_crm_invoice creditnote (cost
=0.29..390.36rows=11647 width=8) (actual time=0.020..4.213 rows=11507 loops=1) 
IndexCond: ((status_key = 'INVOICE_STATUS_INVOICED'::text) AND (sent_date <= 
'2016-06-27'::date)) Heap Fetches: 999 -> Hash (cost=387.15..387.15 rows=11647 
width=8) (actual time=10.063..10.063 rows=11507 loops=1) Buckets: 16384 Batches:
1Memory Usage: 578kB -> Index Only Scan using origo_invoice_id_status_sent_idx 
ononp_crm_invoice inv (cost=0.29..387.15 rows=11647 width=8) (actual time
=0.233..8.133rows=11507 loops=1) Index Cond: ((status_key = 
'INVOICE_STATUS_INVOICED'::text) AND (sent_date <= '2016-06-27'::date)) Heap 
Fetches:999 -> Hash (cost=80.66..80.66 rows=2291 width=8) (actual time
=4.831..4.831rows=2292 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 122kB -> 
Index OnlyScan using onp_crm_relation_pkey on onp_crm_relation com (cost
=0.28..80.66rows=2291 width=8) (actual time=0.235..4.370 rows=2292 loops=1) 
Heap Fetches:1127 -> Hash (cost=14012.31..14012.31 rows=217245 width=4) (actual 
time=281.015..281.015 rows=220147 loops=1) Buckets: 262144 Batches: 1 Memory 
Usage: 9788kB -> Hash Join (cost=433.74..14012.31 rows=217245 width=4) (actual 
time=29.243..235.289 rows=220147 loops=1) Hash Cond: (act.project_id = proj.id) 
-> Seq Scanon onp_crm_activity act (cost=0.00..10591.45 rows=217245 width=8) 
(actualtime=0.186..139.016 rows=220147 loops=1) -> Hash (cost=262.35..262.35 
rows=13711 width=4) (actual time=28.980..28.980 rows=13760 loops=1) Buckets: 
16384Batches: 1 Memory Usage: 612kB -> Index Only Scan using 
onp_crm_project_pkeyon onp_crm_project proj (cost=0.29..262.35 rows=13711 width=
4) (actual time=0.067..26.575 rows=13760 loops=1) Heap Fetches: 6146 Planning 
time: 41.021 ms Execution time: 605.530 ms (60 rows) 
 
That's 420195 / 605 = 695x speedup!
Thanks!
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


Re: [HACKERS] Bogus ANALYZE results for an otherwise-unique column with many nulls

2016-08-07 Thread Andreas Joseph Krogh
På søndag 07. august 2016 kl. 09:01:40, skrev Dean Rasheed <
dean.a.rash...@gmail.com <mailto:dean.a.rash...@gmail.com>>:
On 5 August 2016 at 21:48, Tom Lane <t...@sss.pgh.pa.us> wrote:
 > OK, thanks.  What shall we do about Andreas' request to back-patch this?
 > I'm personally willing to do it, but there is the old bugaboo of "maybe
 > it will destabilize a plan that someone is happy with".
 >

 My inclination would be to back-patch it because arguably it's a
 bug-fix -- at the very least the old behaviour didn't match the docs
 for stadistinct:
 [snip]
 
 
Will this then make it into the soon-to-be-released 9.5.4?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


Re: [HACKERS] Bogus ANALYZE results for an otherwise-unique column with many nulls

2016-08-05 Thread Andreas Joseph Krogh
oice creditnote (cost
=0.28..47.98rows=2140 width=4) (actual time=0.063..0.071 rows=38 loops=4) Heap 
Fetches:0 -> Hash (cost=25.02..25.02 rows=4515 width=8) (actual time
=0.325..0.325rows=119 loops=4) Buckets: 8192 Batches: 1 Memory Usage: 69kB -> 
IndexScan using onp_crm_person_onp_id_idx on onp_crm_person logfor (cost
=0.14..25.02rows=4515 width=8) (actual time=0.032..0.270 rows=119 loops=4) 
Filter: (NOT is_resource) Rows Removed by Filter: 8 Planning time: 2.443 ms 
Executiontime: 239.979 ms (38 rows) 
 
All in all, thanks for looking into this, and +1 for backpatching to 9.5.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-17 Thread Andreas Joseph Krogh
På fredag 17. juni 2016 kl. 08:14:39, skrev Amit Kapila <amit.kapil...@gmail.com
 <mailto:amit.kapil...@gmail.com>>:
On Fri, Jun 17, 2016 at 11:39 AM, Andreas Joseph Krogh <andr...@visena.com 
<mailto:andr...@visena.com>> wrote: På torsdag 16. juni 2016 kl. 20:19:44, 
skrev Tom Lane <t...@sss.pgh.pa.us <mailto:t...@sss.pgh.pa.us>>:
Amit Kapila <amit.kap...@enterprisedb.com <mailto:amit.kap...@enterprisedb.com>
> writes:
 > On Mon, Jun 13, 2016 at 10:36 PM, Tom Lane <t...@sss.pgh.pa.us 
<mailto:t...@sss.pgh.pa.us>> wrote:
 >> min_parallel_relation_size, or min_parallelizable_relation_size, or
 >> something like that?

 > You are right that such a variable will make it simpler to write tests for
 > parallel query.  I have implemented such a guc and choose to keep the name
 > as min_parallel_relation_size.

 Pushed with minor adjustments.  My first experiments with this say that
 we should have done this long ago:
https://www.postgresql.org/message-id/22782.1466100...@sss.pgh.pa.us 
<https://www.postgresql.org/message-id/22782.1466100...@sss.pgh.pa.us>

 > One thing to note is that in function
 > create_plain_partial_paths(), curently it is using PG_INT32_MAX/3 for
 > parallel_threshold to check for overflow, I have changed it to INT_MAX/3 so
 > as to be consistent with guc.c.  I am not sure if it is advisable to use
 > PG_INT32_MAX in guc.c as other similar parameters use INT_MAX.

 I agree that using INT_MAX is more consistent with the code elsewhere in
 guc.c, and more correct given that we declare the variable in question
 as int not int32.  But you need to include  to use INT_MAX ...

 regards, tom lane
 


As of 4c56f3269a84a81461cc53941e0eee02fc920ab6 I'm still getting it in one of 
my queries:
ORDER/GROUP BY expression not found in targetlist
 
I am working on preparing a patch to fix this issue.
 
 
Am I missing something?
 

 No, the fix is still not committed.



 
Ah, I thought Tom pushed a fix, but it apparently was another fix.
 
Thanks for fixing.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

2016-06-17 Thread Andreas Joseph Krogh
På torsdag 16. juni 2016 kl. 20:19:44, skrev Tom Lane <t...@sss.pgh.pa.us 
<mailto:t...@sss.pgh.pa.us>>:
Amit Kapila <amit.kap...@enterprisedb.com> writes:
 > On Mon, Jun 13, 2016 at 10:36 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
 >> min_parallel_relation_size, or min_parallelizable_relation_size, or
 >> something like that?

 > You are right that such a variable will make it simpler to write tests for
 > parallel query.  I have implemented such a guc and choose to keep the name
 > as min_parallel_relation_size.

 Pushed with minor adjustments.  My first experiments with this say that
 we should have done this long ago:
 https://www.postgresql.org/message-id/22782.1466100...@sss.pgh.pa.us

 > One thing to note is that in function
 > create_plain_partial_paths(), curently it is using PG_INT32_MAX/3 for
 > parallel_threshold to check for overflow, I have changed it to INT_MAX/3 so
 > as to be consistent with guc.c.  I am not sure if it is advisable to use
 > PG_INT32_MAX in guc.c as other similar parameters use INT_MAX.

 I agree that using INT_MAX is more consistent with the code elsewhere in
 guc.c, and more correct given that we declare the variable in question
 as int not int32.  But you need to include  to use INT_MAX ...

 regards, tom lane
 
As of 4c56f3269a84a81461cc53941e0eee02fc920ab6 I'm still getting it in one of 
my queries:
ORDER/GROUP BY expression not found in targetlist
  
Am I missing something?
 
I could dig into this further to reproduce if necessary.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


Re: [HACKERS] 10.0

2016-05-13 Thread Andreas Joseph Krogh
På fredag 13. mai 2016 kl. 18:22:00, skrev Magnus Hagander <mag...@hagander.net 
<mailto:mag...@hagander.net>>:
On Fri, May 13, 2016 at 5:42 PM, Andreas Joseph Krogh <andr...@visena.com 
<mailto:andr...@visena.com>> wrote: På fredag 13. mai 2016 kl. 17:05:23, skrev 
Robert Haas <robertmh...@gmail.com <mailto:robertmh...@gmail.com>>:
Hi,

 There is a long-running thread on pgsql-hackers on whether 9.6 should
 instead be called 10.0.  Initially, opinions were mixed, but consensus
 seems now to have emerged that 10.0 is a good choice, with the major
 hesitation being that we've already released 9.6beta1, and therefore
 we might not want to change at this point.  That doesn't seem like an
 insuperable barrier to me, but I think it's now time for the
 discussion on this topic to move here, because:

 1. Some people who have strong opinions may not have followed the
 discussion on pgsql-advocacy, and

 2. If we're going to rebrand this as 10.0, the work will have to get done 
here.

 The major arguments advanced in favor of 10.0 are:

 - There are a lot of exciting features in this release.

 - Even if you aren't super-excited by the features in this release,
 PostgreSQL 9.6/10.0 is a world away from 10.0, and therefore it makes
 sense to bump the version based on the amount of accumulated change
 between then and now.

 Thoughts?  Is it crazy to go from 9.6beta1 to 10.0beta2?  What would
 actually be involved in making the change?
 
>From a non-hacker...
 
>From a DBA/application-developer perspective  while there are many exiting
features in 9.6 I'd expect more from 10.0, like some of these features:
- Built in "Drop-in replacement" Multi-master replication
- Built-in per-database replication with sequences and DDL-changes
  (future versions of pglogical might solve this)
- Full (and effective) parallelism "everywhere"
- Improved executor (like Robert Haas suggested), more use of LLVM or similar
- All of Postgres Pro's GIN-improvements for really fast FTS (with proper, 
index-backed, sorting etc.)
- Pluggable storage-engines
 
 
I'm willing to declare that the likelihood you getting all of these in one 
release is zero. And there will always be "one more feature left".



 
I don't think anyone expects all of them for a 10.0 release:-) I just listed 
some stuff which would, IMHO, validate a 10.0 release, some combined with 
others, others alone (like MMR).
 
-- Andreas Joseph Krogh




Re: [HACKERS] 10.0

2016-05-13 Thread Andreas Joseph Krogh
På fredag 13. mai 2016 kl. 17:05:23, skrev Robert Haas <robertmh...@gmail.com 
<mailto:robertmh...@gmail.com>>:
Hi,

 There is a long-running thread on pgsql-hackers on whether 9.6 should
 instead be called 10.0.  Initially, opinions were mixed, but consensus
 seems now to have emerged that 10.0 is a good choice, with the major
 hesitation being that we've already released 9.6beta1, and therefore
 we might not want to change at this point.  That doesn't seem like an
 insuperable barrier to me, but I think it's now time for the
 discussion on this topic to move here, because:

 1. Some people who have strong opinions may not have followed the
 discussion on pgsql-advocacy, and

 2. If we're going to rebrand this as 10.0, the work will have to get done 
here.

 The major arguments advanced in favor of 10.0 are:

 - There are a lot of exciting features in this release.

 - Even if you aren't super-excited by the features in this release,
 PostgreSQL 9.6/10.0 is a world away from 10.0, and therefore it makes
 sense to bump the version based on the amount of accumulated change
 between then and now.

 Thoughts?  Is it crazy to go from 9.6beta1 to 10.0beta2?  What would
 actually be involved in making the change?
 
>From a non-hacker...
 
>From a DBA/application-developer perspective  while there are many exiting
features in 9.6 I'd expect more from 10.0, like some of these features:
- Built in "Drop-in replacement" Multi-master replication
- Built-in per-database replication with sequences and DDL-changes
  (future versions of pglogical might solve this)
- Full (and effective) parallelism "everywhere"
- Improved executor (like Robert Haas suggested), more use of LLVM or similar
- All of Postgres Pro's GIN-improvements for really fast FTS (with proper, 
index-backed, sorting etc.)
- Pluggable storage-engines
 
Thanks.
 
-- Andreas Joseph Krogh


 


Re: [HACKERS] max_parallel_degree > 0 for 9.6 beta

2016-04-22 Thread Andreas Joseph Krogh
På fredag 22. april 2016 kl. 14:56:33, skrev Robert Haas >:
On Thu, Apr 21, 2016 at 7:20 PM, Tom Lane  wrote:
 > Robert Haas  writes:
 >> On Thu, Apr 21, 2016 at 4:01 PM, Gavin Flower
 >>  wrote:
 >>> Why not 4?  As most processors now have at least 4 physical cores, & 
surely
 >>> it be more likely to flush out race conditions.
 >
 >> Because if we did that, then it's extremely likely that people would
 >> end up writing queries that are faster only if workers are present,
 >> and then not get any workers.
 >
 > Is that because max_worker_processes is only 8 by default?  Maybe we
 > need to raise that, at least for beta purposes?

 I'm not really in favor of that.  I mean, almost all of our default
 settings are optimized for running PostgreSQL on, for example, a
 Raspberry Pi 2, so it would seem odd to suddenly swing the other
 direction and assume that there are more than 8 unused CPU cores.  It
 doesn't make sense to me to roll out settings in beta that we wouldn't
 be willing to release with if they work out.  That's why, honestly, I
 would prefer max_parallel_degree=1, which I think would be practical
 for many real-world deployments.  max_parallel_degree=2 is OK.  Beyond
 that, we're just setting people up to fail, I think.  Higher settings
 should probably only be used on substantial hardware, and not
 everybody has that.
 
Maybe it's time to ask the question if the settings should be optimized more 
for high-end HW and not som matchstick-box? I mean, most of the people I know 
who are responsible for databases run them on HW colser to high-end than 
low-end. I'm not sure why optimizing for low-end is such a great choice.
 
-- Andreas Joseph Krog


 


Re: [HACKERS] max_parallel_degree > 0 for 9.6 beta

2016-04-20 Thread Andreas Joseph Krogh
På onsdag 20. april 2016 kl. 19:46:31, skrev Andres Freund <and...@anarazel.de 
<mailto:and...@anarazel.de>>:
Hi,

 max_parallel_degree currently defaults to 0.  I think we should enable
 it by default for at least the beta period. Otherwise we're primarily
 going to get reports back after the release.

 Then, at the end of beta, we can decide what the default should be.
 
+1
 
Not enabling it per default gives the signal "It's not safe".
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
 <https://www.visena.com>


 


Re: [HACKERS] Covering + unique indexes

2016-04-08 Thread Andreas Joseph Krogh
På lørdag 09. april 2016 kl. 06:34:39, skrev Tom Lane <t...@sss.pgh.pa.us 
<mailto:t...@sss.pgh.pa.us>>:
Andreas Joseph Krogh <andr...@visena.com> writes:
 > Any reason $subject didn't make it (commited but reverted)?

 See the thread on -committers.
 
Ah, thanks.
 
-- Andreas Joseph Krogh




[HACKERS] Covering + unique indexes

2016-04-08 Thread Andreas Joseph Krogh
Any reason $subject didn't make it (commited but reverted)?
 
This is a great feature and lots of work seems to have been put into this 
patch along with quite some reviewing. It would be nice to know why -hackers 
think it's not ready for 9.6.
 
Thanks.
 
-- Andreas Joseph Krogh




Re: [HACKERS] [PATCH] Phrase search ported to 9.6

2016-04-01 Thread Andreas Joseph Krogh
På fredag 01. april 2016 kl. 15:22:55, skrev Teodor Sigaev <teo...@sigaev.ru 
<mailto:teo...@sigaev.ru>>:
> there was a character that was very similar to dots I would suggest
 > that.  The closest is * I think, so what do you think of "***"?

 And join opertator for tsqueries is the same :
 select 'fat'::tsquery *** 'cat'; ?

 Single '*' ?  That's close to regex, any number of tokens. And it saves rules
 about duplicating character.

 select 'fat'::tsquery ** 'cat';
 select 'fat * cat'::tsquery;
 select 'fat * [3] cat'::tsqyery; -- for non-default distance.
 
What about ~> ?
 
-- Andreas Joseph Krogh




Re: [HACKERS] [PATCH] Phrase search ported to 9.6

2016-02-02 Thread Andreas Joseph Krogh
På tirsdag 02. februar 2016 kl. 09:20:06, skrev Oleg Bartunov <
obartu...@gmail.com <mailto:obartu...@gmail.com>>:
    On Tue, Feb 2, 2016 at 10:18 AM, Andreas Joseph Krogh <andr...@visena.com 
<mailto:andr...@visena.com>> wrote: På tirsdag 02. februar 2016 kl. 04:22:57, 
skrev Michael Paquier <michael.paqu...@gmail.com 
<mailto:michael.paqu...@gmail.com>>:
    On Mon, Feb 1, 2016 at 8:21 PM, Dmitry Ivanov <d.iva...@postgrespro.ru 
<mailto:d.iva...@postgrespro.ru>> wrote: This patch was originally developed by 
Teodor Sigaev and Oleg Bartunov in
 2009, so all credit goes to them. Any feedback is welcome. 

This is not a small patch:
 28 files changed, 2441 insertions(+), 380 deletions(-)
And the last CF of 9.6 should not contain rather large patches.
-- Michael


 
 


OTOH; It would be extremely nice to get this into 9.6.
 
will see how community decided.
anyway, it's already in our distribution.



 
 
Which seems to indicate it has received a fair amount of testing and is quite 
stable.
Hopefully it integrates into the 9.6 codebase without too much risk.
Thanks for contributing this.
 
-- Andreas Joseph Krogh




Re: [HACKERS] [PATCH] Phrase search ported to 9.6

2016-02-02 Thread Andreas Joseph Krogh
På tirsdag 02. februar 2016 kl. 12:04:21, skrev Alvaro Herrera <
alvhe...@2ndquadrant.com <mailto:alvhe...@2ndquadrant.com>>:
Andreas Joseph Krogh wrote:
   
 > Which seems to indicate it has received a fair amount of testing and is 
quite
 > stable.
 > Hopefully it integrates into the 9.6 codebase without too much risk.

 Yes, yes, that's all very good, but we're nearing the closure of the 9.6
 development cycle and we only have one commitfest left.  If someone had
 lots of community brownie points because of doing lots of reviews of
 other people's patches, they might push their luck by posting this patch
 to the final commitfest.  But if that someone didn't, then it wouldn't
 be fair, and if I were the commitfest manager of that commitfest I would
 boot their patch to the 9.7-First commitfest.

 The current commitfest which I'm trying to close still has 24 patches in
 needs-review state and 11 patches ready-for-committer; the next one (not
 closed yet) has 40 patches that will need review.  That means a total of
 75 patches, and those should all be processed ahead of this one.  The
 effort needed to process each of those patches is not trivial, and I'm
 sorry I have to say this but I don't see PostgresPro contributing enough
 reviews, even though I pinged a number of people there, so putting one
 more patch on the rest of the community's shoulders doesn't seem fair to
 me.

 Everybody has their favorite patch that they want in the next release,
 but we only have so much manpower to review and integrate those patches.
 All review help is welcome.
 
I understand completely.
 
-- Andreas Joseph Krogh




Re: [HACKERS] [PATCH] Phrase search ported to 9.6

2016-02-01 Thread Andreas Joseph Krogh
På tirsdag 02. februar 2016 kl. 04:22:57, skrev Michael Paquier <
michael.paqu...@gmail.com <mailto:michael.paqu...@gmail.com>>:
    On Mon, Feb 1, 2016 at 8:21 PM, Dmitry Ivanov <d.iva...@postgrespro.ru 
<mailto:d.iva...@postgrespro.ru>> wrote: This patch was originally developed by 
Teodor Sigaev and Oleg Bartunov in
 2009, so all credit goes to them. Any feedback is welcome. 

This is not a small patch:
 28 files changed, 2441 insertions(+), 380 deletions(-)
And the last CF of 9.6 should not contain rather large patches.
-- Michael


 
 
OTOH; It would be extremely nice to get this into 9.6.
 
 
-- Andreas Joseph Krogh




Re: [HACKERS] Support for detailed description of errors cased by trigger-violations

2014-11-09 Thread Andreas Joseph Krogh
På lørdag 08. november 2014 kl. 23:39:50, skrev Tom Lane t...@sss.pgh.pa.us 
mailto:t...@sss.pgh.pa.us: Andreas Joseph Krogh andr...@visena.com writes:
  Hi. �� When working with Oracle it is possible to catch 
constraint-violations
  caused by triggers using JDBC, but it seems this isn't possible using PG, 
see
  this thread:
  https://github.com/impossibl/pgjdbc-ng/issues/111#issuecomment-62276464

 I'm not exactly following the point.  The complaint seems to be that

     RAISE EXCEPTION 'ID must be less then 10';

 doesn't send anything except the given primary message and a generic
 SQLSTATE.  Well, duh: it's not supposed to.  There are a bunch of options
 you can supply in RAISE to populate additional fields of the error report.
 For example, you could add

     USING SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME

 if you wanted the report to name the table the trigger is attached to.

 So it seems to me this is lazy plpgsql programming, not a missing feature.
 It would only be a missing feature if you think plpgsql should try to
 auto-populate these fields; but I'd be against that because it would
 require too many assumptions about exactly what the function might be
 complaining about.

 regards, tom lane   This is fantastic, thanks Tom! It indeed was sloppy 
plpgsql programming. I didn't know about these extra arguments of RAISE making 
it possible to fine-tune the error-report from triggers.   Very nice and thanks 
again for making me look the right place.   -- Andreas Joseph Krogh CTO / 
Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com 
mailto:andr...@visena.com www.visena.com https://www.visena.com  
https://www.visena.com  

[HACKERS] Support for detailed description of errors cased by trigger-violations

2014-11-08 Thread Andreas Joseph Krogh
Hi.   When working with Oracle it is possible to catch constraint-violations 
caused by triggers using JDBC, but it seems this isn't possible using PG, see 
this thread: 
https://github.com/impossibl/pgjdbc-ng/issues/111#issuecomment-62276464   For 
check of FK-violations the protocol supports this fine, with details about 
which table, column etc. causing the violation. Is there any work going on or 
are there any plans to support similar info for violations caused by triggers?  
Thanks.   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 
963 andr...@visena.com mailto:andr...@visena.com www.visena.com 
https://www.visena.com  https://www.visena.com

Re: [HACKERS] nulls in GIN index

2014-08-11 Thread Andreas Joseph Krogh
På mandag 11. august 2014 kl. 11:17:56, skrev worthy7 worthy@gmail.com 
mailto:worthy@gmail.com: Perhaps I'm missing something

 Table has 2 columns, text and ftstext

 text: how are you
 ftstest: (nothing)

 Because how and are and you are too common to be tsvectored. Which is
 fine.

 So if a user searches for how are you:
 select * from tbl_lines WHERE
 ftstext @@ plainto_tsquery('English', 'how are you')

 Returns nothing. Which I somewhat understand, but I want it to return all
 the rows with nothing in the ftstext.
 plainto_tsquery('English', 'how are you') = ''
 and the ftstext of some rows is also = ''
 So why doesn't the index return all these rows when a null string is
 searched.

 I think you can see what im trying to achieve, how do I do it?   Use the 
'simple' dictionary:   my_fts_column @@ to_tsquery('simple', 'how are you')   --
Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 
andr...@visena.com mailto:andr...@visena.com www.visena.com 
https://www.visena.com  https://www.visena.com  

[HACKERS] Setting PG-version without recompiling

2014-07-03 Thread Andreas Joseph Krogh
Hi.   I'm up for testing 9.4 but my JDBC-driver fails to connect due to PG's 
minor-version string: 4beta1. Is it possible to set this somewhere without 
recompiling PG?   Thanks.   -- Andreas Jospeh Krogh CTO / Partner - Visena AS 
Mobile: +47 909 56 963 andr...@visena.com mailto:andr...@visena.com 
www.visena.com https://www.visena.com  https://www.visena.com

Re: [HACKERS] Setting PG-version without recompiling

2014-07-03 Thread Andreas Joseph Krogh
På torsdag 03. juli 2014 kl. 11:13:44, skrev Abhijit Menon-Sen 
a...@2ndquadrant.com mailto:a...@2ndquadrant.com: At 2014-07-03 11:02:34 
+0200, andr...@visena.com wrote:
 
  Is it possible to set this somewhere without
  recompiling PG?

 I'm afraid not.   Ok   -- Andreas Jospeh Krogh CTO / Partner - Visena AS 
Mobile: +47 909 56 963 andr...@visena.com mailto:andr...@visena.com 
www.visena.com https://www.visena.com  https://www.visena.com  

Re: [HACKERS] Setting PG-version without recompiling

2014-07-03 Thread Andreas Joseph Krogh
På torsdag 03. juli 2014 kl. 16:16:01, skrev Tom Lane t...@sss.pgh.pa.us 
mailto:t...@sss.pgh.pa.us: Andreas Joseph Krogh andr...@visena.com writes:
  Hi. �� I'm up for testing 9.4 but my JDBC-driver fails to connect due to 
PG's
  minor-version string: 4beta1. Is it possible to set this somewhere without
  recompiling PG?

 No, and even if you could, that would be the wrong approach.  The right
 approach is to fix the JDBC driver to not complain about such version
 strings.  I'm a bit surprised they haven't done so long since, considering
 how long PG beta versions have been tagged like that.  For that matter,
 they really ought not complain about strings like 9.5devel or
 9.5alpha2 either.

 regards, tom lane   I'm using the pgjdbc-ng driver, I'm sure the official 
driver handles this. The driver will be fixed (the issue has a pull-request), I 
just wondered if there was a magic know I could use until the PR is merged.   --
Andreas Jospeh Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 
andr...@visena.com mailto:andr...@visena.com www.visena.com 
https://www.visena.com  https://www.visena.com  

Re: [HACKERS] BDR-project

2014-01-08 Thread Andreas Joseph Krogh
På onsdag 08. januar 2014 kl. 18:57:52, skrev Simon Riggs si...@2ndquadrant.com
 mailto:si...@2ndquadrant.com: On 25 December 2013 12:01, Andreas Joseph 
Krogh andr...@officenet.no wrote:

  Ref:
  http://wiki.postgresql.org/wiki/BDR_Project
 
  Is implementing main BDR features into core Postgres a probable objective to
  version 9.4?

 I've not updated hackers recently on this point, so thanks for asking
 the question. This was discussed in the ending keynote of the PG
 Europe conference, but I appreciate that's not the same thing as
 saying it here.

 The plan is

 * submit the core logical replication technology for 9.4
 * submit online upgrade as a feature for 9.5, allowing upgrades from 9.4+
 * submit full BDR features for 9.6

 BDR code will be released as a separate open source project until/if
 core accepts/modifies that. There's lots of work and discussion to be
 had yet, so the above plan is a reasonable schedule for achieving
 change allowing input from all. Design to full feature submission
 would be 4.5 years, plus we expect the features to mature/extend after
 that, so there's no rush, just steady movement.

 No attempts to publicise that as yet, but if all goes well we expect
 to do that once 9.4 is released.   Thanks for the update!   --
 Andreas Joseph Krogh andr...@officenet.no      mob: +47 909 56 963
 Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
 Public key: http://home.officenet.no/~andreak/public_key.asc  

[HACKERS] BDR-project

2013-12-25 Thread Andreas Joseph Krogh
Hi hackers.   Ref: http://wiki.postgresql.org/wiki/BDR_Project   Is 
implementing main BDR features into core Postgres a probable objective to 
version 9.4?   Thanks.   --
 Andreas Joseph Krogh andr...@officenet.no      mob: +47 909 56 963
 Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
 Public key: http://home.officenet.no/~andreak/public_key.asc

Re: [HACKERS] Covering Indexes

2012-06-28 Thread Andreas Joseph Krogh

On 06/28/2012 02:16 PM, David E. Wheeler wrote:

Hackers,

Very interesting design document for SQLite 4:

   http://www.sqlite.org/src4/doc/trunk/www/design.wiki

I'm particularly intrigued by covering indexes. For example:

 CREATE INDEX cover1 ON table1(a,b) COVERING(c,d);

This allows the following query to do an index-only scan:

 SELECT c, d FROM table1 WHERE a=? AND b=?;

Now that we have index-only scans in 9.2, I'm wondering if it would make sense 
to add covering index support, too, where additional, unindexed columns are 
stored alongside indexed columns.

And I wonder if it would work well with expressions, too?

David


This is analogous to SQL Server's include :

|CREATE NONCLUSTERED INDEX my_idx|
|ON my_table (status)|
|INCLUDE (someColumn, otherColumn)|

Which is useful, but bloats the index.

--
Andreas Joseph Kroghandr...@officenet.no  - mob: +47 909 56 963
Senior Software Developer / CEO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc



Re: [HACKERS] WIP: SP-GiST, Space-Partitioned GiST

2011-09-06 Thread Andreas Joseph Krogh
On 09/06/2011 07:34 PM, Oleg Bartunov wrote:
 Here is the latest spgist patch, which has all planned features as
 well as
 all overhead, introduced by concurrency and recovery, so performance
 measurement should be realistic now.

 Oleg

Sorry for not getting the might-be-obvious here, but will this patch
bring indexed substring-search to PG? So queries conceptually equal to
this will be possible to index: WHERE som_col @@
':substr1::substr2!substr3:' meaning contains substr1 AND ends with
substr2 OR starts with substr3?

-- 
Andreas Joseph Krogh andr...@officenet.no - mob: +47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc


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


Re: [HACKERS] potential bug in trigger with boolean params

2011-05-11 Thread Andreas Joseph Krogh
På onsdag 11. mai 2011 kl 10:56:19 skrev t...@fuzzy.cz:
  Hi,
  I was trying to create a trigger with parameters. I've found a potential
  bug
  when the param is boolean.
 
  Here is code replicating the bug:
 
  CREATE TABLE x(x TEXT);
 
  CREATE OR REPLACE FUNCTION trigger_x() RETURNS TRIGGER AS $$
  BEGIN
  RETURN NEW;
  END; $$ LANGUAGE PLPGSQL;
 
  CREATE TRIGGER trig_x_text BEFORE INSERT ON x FOR EACH ROW EXECUTE
  PROCEDURE
  trigger_x('text');
  CREATE TRIGGER trig_x_int BEFORE INSERT ON x FOR EACH ROW EXECUTE
  PROCEDURE
  trigger_x(10);
  CREATE TRIGGER trig_x_float BEFORE INSERT ON x FOR EACH ROW EXECUTE
  PROCEDURE trigger_x(42.0);
  CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE
  PROCEDURE
  trigger_x(true);
 
  ERROR:  syntax error at or near true
  LINE 1: ... INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(true);
 
 The docs clearly state what the valid values are and the literal 'true' is
 not one of them (TRUE is). See this:
 
 http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html

What are you trying to accomplish? CREATE OR REPLACE FUNCTION trigger_x() 
does not declare any formal-parameters, so calling it with arguments doesn't 
make sense. I'm surprised creating the other triggers didn't produce an error 
stating No function defined with the name trigger_ix and the given 
argument-type.

--
Andreas Joseph Krogh andr...@officenet.no
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
Org.nr: NO 981 479 076  | |
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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


Re: [HACKERS] potential bug in trigger with boolean params

2011-05-11 Thread Andreas Joseph Krogh
P onsdag 11. mai 2011 kl 11:30:51 skrev Szymon Guz mabew...@gmail.com:
 

On 11 May 2011 11:01, Andreas Joseph Krogh andr...@officenet.no wrote:
 P onsdag 11. mai 2011 kl 10:56:19 skrev t...@fuzzy.cz:


  Hi,
  I was trying to create a trigger with parameters. I've found a potential
  bug
  when the param is boolean.
 
  Here is code replicating the bug:
 
  CREATE TABLE x(x TEXT);
 
  CREATE OR REPLACE FUNCTION trigger_x() RETURNS TRIGGER AS $$
  BEGIN
  RETURN NEW;
  END; $$ LANGUAGE PLPGSQL;
 
  CREATE TRIGGER trig_x_text BEFORE INSERT ON x FOR EACH ROW EXECUTE
  PROCEDURE
  trigger_x('text');
  CREATE TRIGGER trig_x_int BEFORE INSERT ON x FOR EACH ROW EXECUTE
  PROCEDURE
  trigger_x(10);
  CREATE TRIGGER trig_x_float BEFORE INSERT ON x FOR EACH ROW EXECUTE
  PROCEDURE trigger_x(42.0);
  CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE
  PROCEDURE
  trigger_x(true);
 
  ERROR: syntax error at or near true
  LINE 1: ... INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(true);

 The docs clearly state what the valid values are and the literal 'true' is
 not one of them (TRUE is). See this:

 http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html


What are you trying to accomplish? CREATE OR REPLACE FUNCTION trigger_x() does not declare any formal-parameters, so calling it with arguments doesn't make sense. I'm surprised creating the other triggers didn't produce an error stating No function defined with the name trigger_ix and the given argument-type.



That's how you define trigger function. Later you can use params when defining trigger.


Pardon my ignorance:-)

--
Andreas Joseph Krogh andr...@officenet.no
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS  | The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch|
1414 Trollsen | somebody else doing it wrong, without   |
NORWAY | comment.  |
Org.nr: NO 981 479 076 |  |
|  |
Tlf:  +47 24 15 38 90 |  |
Fax:  +47 24 15 38 91 |  |
Mobile: +47 909 56 963 |  |
+-+



Re: [HACKERS] accentuated letters in text-search

2010-07-22 Thread Andreas Joseph Krogh

On 07/22/2010 07:42 AM, Guillaume Lelarge wrote:

Le 21/07/2010 23:23, Andreas Joseph Krogh a écrit :
   

[...]
I was googling for how to create a text-seach-config with the following
properties:
- Map unicode accentuated letters to an un-accentuated equivalent
- No stop-words
- Lowercase all words

And came over this from -general:
http://www.techienuggets.com/Comments?tx=106813

Then after some more googling I found this:
http://www.sai.msu.su/~megera/wiki/unaccent

Any reason the unaccent dict. and function did not make it in 9.0?

 

Well, AFAICT, it's available in 9.0:

   http://www.postgresql.org/docs/9.0/static/unaccent.html
   


My contrib-foo was pretty low last night it seems, sorry for the noise...

--
Andreas Joseph Kroghandr...@officenet.no
Senior Software Developer / CTO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+


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


[HACKERS] accentuated letters in text-search

2010-07-21 Thread Andreas Joseph Krogh

Hi.

I was googling for how to create a text-seach-config with the following 
properties:

- Map unicode accentuated letters to an un-accentuated equivalent
- No stop-words
- Lowercase all words

And came over this from -general:
http://www.techienuggets.com/Comments?tx=106813

Then after some more googling I found this:
http://www.sai.msu.su/~megera/wiki/unaccent

Any reason the unaccent dict. and function did not make it in 9.0?

--
Andreas Joseph Kroghandr...@officenet.no
Senior Software Developer / CTO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+


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


Re: [HACKERS] 8.5 vs. 9.0

2010-01-22 Thread Andreas Joseph Krogh
On Friday 22. January 2010 01.22.09 Tom Lane wrote:
 Larry Rosenman l...@lerctr.org writes:
  On Thu, January 21, 2010 5:53 pm, Andreas Joseph Krogh wrote:
  Care to shed some light on what features (yes, we users care about
  features) warrant this major version-bump? Is there a link somewhere?
 
  AFAIR, it was stated if Hot Standby AND Streaming Replication hit the
  tree, the release number would go to 9.0.
 
 Yeah.  The question of when do we call it 9.0 has come up multiple
 times over the past few release cycles, and when we get built-in
 replication has always been one of the more popular answers.  If HS+SR
 aren't enough to justify a major version bump, I'm not sure what would be.
 
 The other bit of rationale for this is that HS+SR are likely to induce a
 certain amount of, um, instability.  Labeling the release with a dot-oh
 version number will help to set people's expectations about that.  For
 comparison's sake, one of the main reasons for calling 8.0 8.0 was the
 native Windows port, and it certainly took a while for that to settle
 down.

Thank you for the enlightening reply.

-- 
Andreas Joseph Krogh andr...@officenet.no
Senior Software Developer / CTO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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


Re: [HACKERS] 8.5 vs. 9.0

2010-01-21 Thread Andreas Joseph Krogh
On Thursday 21. January 2010 10.37.41 Dave Page wrote:
 In an attempt to pre-empt the normally drawn-out discussions about
 what the next version of PostgreSQL will be numbered. the core team
 have discussed the issue and following a lenghty debate lasting
 literally a few minutes decided that the next release shall be
 
 Wait for it
 
 9.0.

Care to shed some light on what features (yes, we users care about features) 
warrant this major version-bump? Is there a link somewhere?

-- 
Andreas Joseph Krogh andr...@officenet.no
Senior Software Developer / CTO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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


Re: [HACKERS] NOT NULL violation and error-message

2010-01-12 Thread Andreas Joseph Krogh
On Tuesday 12. January 2010 11.10.09 Takahiro Itagaki wrote:
 
 Andreas Joseph Krogh andr...@officenet.no wrote:
 
  ERROR: null value in column created violates not-null constraint
 
 It is easy to add the table name to the message, but ...
 
  ERROR: null value in column public.mytable.created violates not-null 
  constraint
  Oracle does this btw...
 
 Do we have any guideline about the message for identifier names? We've
 already had serveral table.column messages, but schema.table.column
 might be preferred if there are tables with the same name in different
 schema. In addition, separated quotes (schema.table.column) are
 more SQL-ish than single outer quotes. Which should we use?
 
 At any rate, we need to adjust many regression test and .po files
 if we change such kinds of messages.
 
 
 Index: src/backend/executor/execMain.c
 ===
 --- src/backend/executor/execMain.c   (HEAD)
 +++ src/backend/executor/execMain.c   (fixed)
 @@ -1316,7 +1316,8 @@
   slot_attisnull(slot, attrChk))
   ereport(ERROR,
   
 (errcode(ERRCODE_NOT_NULL_VIOLATION),
 -  errmsg(null value in column 
 \%s\ violates not-null constraint,
 +  errmsg(null value in column 
 \%s.%s\ violates not-null constraint,
 + RelationGetRelationName(rel),
   
 NameStr(rel-rd_att-attrs[attrChk - 1]-attname;
   }
   }

+1

-- 
Andreas Joseph Krogh andr...@officenet.no
Senior Software Developer / CTO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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


[HACKERS] NOT NULL violation and error-message

2010-01-08 Thread Andreas Joseph Krogh
Hi all, when trying to insert/update a NOT NULL column with a null-values (in 
this case the created-column), we get this error:

ERROR: null value in column created violates not-null constraint

Using JDBC this error-message is what appears in the SQLException.getMessage() 
which makes it impossible to tell which table the NOT_NULL_VIOLATION happened 
in. This can be a real pain as this might happen upon transaction-commit using 
OR-tools like Hibernate. The transaction might involve updating several tables 
with the same column-name rendering it impossible to extract what the error 
really is from the error-message. Is there a way to prefix the column-name in 
the error-message with table-name and maybe also schema-name? The message would 
then instead read something like:

ERROR: null value in column public.mytable.created violates not-null 
constraint

Oracle does this btw...

-- 
Andreas Joseph Krogh andr...@officenet.no
Senior Software Developer / CTO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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


Re: [HACKERS] Windowing Function Patch Review - ROW_NUMBER without ORDER BY

2008-11-09 Thread Andreas Joseph Krogh
On Sunday 09 November 2008 22:35:01 David Rowley wrote:
 I've been trying to think of a use case for using ROW_NUMBER() with no ORDER
 BY in the window clause. 
 
 Using the example table I always seem to be using, for those who missed it
 in other threads.
 
 create table employees (
   id INT primary key,
   name varchar(30) not null,
   department varchar(30) not null,
   salary int not null,
   check (salary = 0)
 );
 
 insert into employees values(1,'Jeff','IT',1);
 insert into employees values(2,'Sam','IT',12000);
 insert into employees values(3,'Richard','Manager',3);
 insert into employees values(4,'Ian','Manager',2);
 insert into employees values(5,'John','IT',6);
 insert into employees values(6,'Matthew','Director',6);
 
 
 david=# select *,row_number() over () from employees;
  id |  name   | department | salary | row_number
 +-+++
   1 | Jeff| IT |  1 |  1
   2 | Sam | IT |  12000 |  2
   4 | Ian | Manager|  2 |  3
   5 | John| IT |  6 |  4
   6 | Matthew | Director   |  6 |  5
   3 | Richard | Manager|  3 |  6
 (6 rows)
 
 row_number seems to assign the rows a number in order of how it reads them
 from the heap. Just to confirm...
 
 update employees set salary = salary where id = 3;
 
 david=# select *,row_number() over () from employees;
  id |  name   | department | salary | row_number
 +-+++
   1 | Jeff| IT |  1 |  1
   2 | Sam | IT |  12000 |  2
   4 | Ian | Manager|  2 |  3
   5 | John| IT |  6 |  4
   6 | Matthew | Director   |  6 |  5
   3 | Richard | Manager|  3 |  6
 (6 rows)
 
 The spec says: The ROW_NUMBER function computes the sequential row number,
 starting with 1 (one) for the first row, of the row within its window
 partition according to the window ordering of the window.
 
 I'm just not sure if we should block this or not. 
 
 Does anyone see this as a feature?
 
 Does anyone see this as a bug?
 
 Any feedback is welcome

I see this as a greate feature.
It will hopefully be possible to write:

SELECT *, max(row_number()) over() as total_rows from employees;

To get the maximum number of rows in a separate column. Very usefull when 
writing queries to retrieve paged results. Like Give me the 20 top articles 
sorted on date and also the total number of articles in *one* query, 
eliminating the need for a separate count(*) query.

There was some discussion regarding this here:
http://archives.postgresql.org/pgsql-hackers/2008-10/msg00729.php

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / CEO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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


[HACKERS] Window Functions

2008-10-14 Thread Andreas Joseph Krogh
Hi all.
This is not very hackers-related, but related to the topic of 
window-funcitons, which seems to be discussed quite a bit on hackers these 
days.

Can window-functions in PG be used to return total number of rows in a paged 
result?
Say you have:
SELECT p.id, p.firstname
  FROM person p
 ORDER BY p.firstname ASC
 LIMIT 10 OFFSET 10

Is it possible to use some window-function to return the total-number of 
columns in a separate column?

In Oracle one can do 
SELECT q.*, max(rownum) over() as total_rows FROM (subquery)
which returns the total number or columns in a separate column. This is very 
handy for web-pages which for example need to display the rist 20 results of 
several million, without having to do a separate count(*) query.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / CEO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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


Re: [HACKERS] Window Functions

2008-10-14 Thread Andreas Joseph Krogh
On Tuesday 14 October 2008 18:19:07 Hannu Krosing wrote:
 On Tue, 2008-10-14 at 11:05 +0200, Andreas Joseph Krogh wrote:
  Hi all.
  This is not very hackers-related, but related to the topic of 
  window-funcitons, which seems to be discussed quite a bit on hackers 
  these days.
  
  Can window-functions in PG be used to return total number of rows in a 
  paged result?
  Say you have:
  SELECT p.id, p.firstname
FROM person p
   ORDER BY p.firstname ASC
   LIMIT 10 OFFSET 10
  
  Is it possible to use some window-function to return the total-number of 
  columns in a separate column?
  
  In Oracle one can do 
  SELECT q.*, max(rownum) over() as total_rows FROM (subquery)
  which returns the total number or columns in a separate column. This is 
  very handy for web-pages which for example need to display the rist 20 
  results of several million, without having to do a separate count(*) query.
 
 no need to use window functions here, just ask for max inline:
 
 
 hannu=# select rownum, word, (select max(rownum) from words) as maxrow
 from words limit 10;
  rownum |   word| maxrow 
 +---+
   1 |   |  98569
   2 | A |  98569
   3 | A's   |  98569
   4 | AOL   |  98569
   5 | AOL's |  98569
   6 | Aachen|  98569
   7 | Aachen's  |  98569
   8 | Aaliyah   |  98569
   9 | Aaliyah's |  98569
  10 | Aaron |  98569
 (10 rows)

Where do you get your rownum-column from here? It's a pseudo-column in Oracle 
which is computed for each row in the result-set, it's not a column in a 
table somewhere, which is why I figured I must use window-funciton, or 
analytical function as Oracle calls them, to operate on the *result-set* to 
retrieve the maximum number of rows which satisfies the query.

As far as I understand the ROW_NUMBER() window-funciton can be used to 
construct limit with offset-queries in a SQL-spec-compliant way.

Say I want to retrieve an ordered list of persons (by name):

SELECT * FROM (
SELECT ROW_NUMBER() OVER (order by p.name) as rnum, q.*
  FROM (
SELECT p.id, p.name FROM person p where p.birth_date  '2000-01-01'
) q
) r
 WHERE r.rnum between 11 AND 20
;

This is good and works in Oracle, PG = 8.4 and others that implements 
spec-compliant window-functions. This is fine, but in Oracle I can extend this 
query to this for getting the total-number (not just the page 11-20) of 
persons matching in a separate column:

SELECT * FROM (
SELECT ROW_NUMBER() OVER (order by p.name) as rnum, q.*, max(rownum) over() as 
total_rows
  FROM (
SELECT p.id, p.name FROM person p where p.birth_date  '2000-01-01'
) q
) r
 WHERE r.rnum between 11 AND 20
;

So my question is: Will PG, with window functions, provide a similar mechanism 
for retrieving the total number of rows in the result-set without actually 
retrieving them all? I understand that PG might have to visit them all in order 
to retrieve that count, but that's OK.

What I'm looking for is an elegant solution to what's becomming a more common 
requirement in web-applications these days: To display pageable lists with a 
total-count, and to do that with *one* query, preferrably using 
standard-compliant SQL.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / CEO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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


Re: [HACKERS] Identifier case folding notes

2008-07-09 Thread Andreas Joseph Krogh
On Wednesday 09 July 2008 00:35:07 Tom Lane wrote:
 Andreas Joseph Krogh [EMAIL PROTECTED] writes:
  Right. From a user's perspective 4) sounds best. I often run into problems 
  having keywords as column-names:
 
 None of the proposals on the table will remove the need to use quotes in
 that case.

I know, but then tools/frameworks won't fail when they produce queries like 
SELECT USER FROM test, because it sends USER and not user.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

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


Re: [HACKERS] Identifier case folding notes

2008-07-08 Thread Andreas Joseph Krogh
On Tuesday 08 July 2008 23:04:51 Josh Berkus wrote:
 Tom,
 
  IMHO, practically the only solid argument for changing from the way
  we do things now is to meet the letter of the spec.  The various sorts
  of gamesmanship you list would most definitely not meet the letter of
  the spec; between that and the inevitability of breaking some apps,
  I'm inclined to reject them all on sight.
 
 Actually, there are a number of *very* popular database tools, particularly 
 in the Java world (such as Netbeans and BIRT) which do mix quoted and 
 unquoted identifiers.  In general, users of those tools reject PostgreSQL 
 as broken for our nonstandard behavoir rather than trying to work around 
 it.
 
 So it's not just a standards issue; this problem really *is* hurting us in 
 adoption.
 
 -- 
 --Josh
 
 Josh Berkus
 PostgreSQL @ Sun
 San Francisco

Right. From a user's perspective 4) sounds best. I often run into problems 
having keywords as column-names:

andreak=# create table test(user varchar);
ERROR:  syntax error at or near user
LINE 1: create table test(user varchar);
  ^
andreak=# create table test(user varchar);
CREATE TABLE
andreak=# insert into test(USER) values('testuser');
ERROR:  column USER of relation test does not exist
LINE 1: insert into test(USER) values('testuser');
 ^
andreak=# insert into test(user) values('testuser');
ERROR:  syntax error at or near user
LINE 1: insert into test(user) values('testuser');
 ^
andreak=# insert into test(user) values('testuser');
INSERT 0 1

As you know, the only way of referring to the user-column is to qoute it in 
lowercase, which many apps and tools don't do.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager

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


Re: [HACKERS] Sorting Improvements for 8.4

2007-12-19 Thread Andreas Joseph Krogh
On Tuesday 18 December 2007 10:03:25 Dimitri Fontaine wrote:
 Hi,

 Le mardi 18 décembre 2007, Ron Mayer a écrit :
  Has anyone looked into sorting algorithms that could use
  more than one CPU or core at a time?

 [...]

  PS: Yeah, I know multi-threading is a hot-button on these
  lists; but sorting seems a relatively isolated of the code
  and I'd wonder if it'd be isolate-able enough that multiple
  CPUs could be used there.

 And before that objection to multi-threading implementation and portability
 concerns arise, what about using a coroutine BSD-licenced portable
 implementation such as Protothreads to have backend code use several CPU at
 a time?
   http://www.sics.se/~adam/pt/

 With such a tool, would it be possible to think about producer/consumer
 parallel executions for sorting, aggregates nodes or other parts of the
 executor?

 Hope this helps, regards,

And remember; Users don't care about portability-issues, they care about 
performance. If multi-threading is a way to speed up sorting considerably, it 
should, IMHO, be considered seriously.

--
Andreas Joseph Krogh

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] Javascript support in the backend, i.e. PL/JS

2007-11-16 Thread Andreas Joseph Krogh
On Friday 16 November 2007 11:29:09 Sam Mason wrote:
[snip]
 SP?

Stored Procedure

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] Javascript support in the backend, i.e. PL/JS

2007-11-16 Thread Andreas Joseph Krogh
On Friday 16 November 2007 09:10:43 Sam Mason wrote:
 Hi All,

 I've been writing some code[1] to support Javascript in the backend.
[snip]

Wow, this is supercool!

Most people, as you probably know, don't like JS as a language 'cause they 
think of it as a web-browser language with lots of bad side-effects, but 
that's 'cause they don't know the language, really. JS actually has some nice 
programming concepts and being able to use it as an SP seems pretty 
attractive. Keep up the good work!

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] Javascript support in the backend, i.e. PL/JS

2007-11-16 Thread Andreas Joseph Krogh
On Friday 16 November 2007 12:23:26 Sam Mason wrote:
 On Fri, Nov 16, 2007 at 12:05:02PM +0100, Andreas Joseph Krogh wrote:
  On Friday 16 November 2007 11:29:09 Sam Mason wrote:
  [snip]
 
   SP?
 
  Stored Procedure

 That was kind of obvious wasn't it!  I failed to parse that because
 of the an before it; an stored procedure doesn't make much sense!
 English is a great language isn't it. :)

Yea, I wrote an 'cause I pronounce it ess pee...

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] psql show dbsize?

2007-11-01 Thread Andreas Joseph Krogh
On Thursday 01 November 2007 00:44:16 Tom Lane wrote:
 Andrew Dunstan [EMAIL PROTECTED] writes:
  Perhaps both these considerations dictate providing another command or a
  special flavor of \l instead of just modifying it?

 I've seen no argument made why \l should print this info at all.

   regards, tom lane

What about \l+ ?
The '+' is already in \d, so it's a known feature, and then people wanting 
more info from \l can use \l+.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] Including Snapshot Info with Indexes

2007-10-12 Thread Andreas Joseph Krogh
Will $SUBJECT make it possible for count(*) to use index? This is a much 
wanted feature.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] Including Snapshot Info with Indexes

2007-10-12 Thread Andreas Joseph Krogh
On Friday 12 October 2007 11:49:17 Heikki Linnakangas wrote:
 Andreas Joseph Krogh wrote:
  Will $SUBJECT make it possible for count(*) to use index? This is a much
  wanted feature.

 Yes, both the DSM approach and the approach proposed by Gokul.

Good.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [HACKERS] 8.3 version of ts_headline

2007-09-18 Thread Andreas Joseph Krogh
On Tuesday 18 September 2007 17:15:43 Teodor Sigaev wrote:
 in 8.2
 SELECT headline('default', 'a b c', 'c'::tsquery,
 E'StartSel=span class=\\style1\\, StopSel=/span');

  In 8.2 this produces an error:
 
  SELECT headline('default', 'a b c', 'c'::tsquery,
  'StartSel=span class=style1, StopSel=/span');
  ERROR:  syntax error

Aha, thanks!

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] 8.3 version of ts_headline

2007-09-15 Thread Andreas Joseph Krogh
On Friday 14 September 2007 20:26:28 Tom Lane wrote:
 Andreas Joseph Krogh [EMAIL PROTECTED] writes:
  In 8.2 this produces an error:
 
  SELECT headline('default', 'a b c', 'c'::tsquery,
  'StartSel=span class=style1, StopSel=/span');
  ERROR:  syntax error
  DETAIL:  Syntax error in position 15.

 Sure you don't just need to quote the values?

 regression=# SELECT ts_headline('english', 'a b c', 'c'::tsquery,
 $$StartSel='span class=style1', StopSel='/span'$$);
 ts_headline
 ---
  a b span class=style1c/span
 (1 row)


   regards, tom lane

Doesn't work in 8.2:

andreak=# SELECT headline('default', 'a b c', 'c'::tsquery,
$$StartSel='span class=style1', StopSel='/span'$$);
ERROR:  syntax error
DETAIL:  Syntax error in position 16.
andreak=# select version();
version

 PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 
(Ubuntu 4.1.2-0ubuntu4)
(1 row)


-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


[HACKERS] 8.3 version of ts_headline

2007-09-14 Thread Andreas Joseph Krogh
Hi.
In 8.2 this produces an error:

SELECT headline('default', 'a b c', 'c'::tsquery,
'StartSel=span class=style1, StopSel=/span');
ERROR:  syntax error
DETAIL:  Syntax error in position 15.

while this works:
SELECT headline('default', 'a b c', 'c'::tsquery,
'StartSel=b, StopSel=/b');
   headline
--
 a b bc/b
(1 row)

In the modern age people use stylesheets and it seems awkward that it's not 
possible to highlight headlines using stylesheets in PG. Is this intentional, 
or a glitch? Is it possible to extend ts_headline to support more advanced 
markup in StartSel?

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


[HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andreas Joseph Krogh
This has been been discussed before, but Oracle behaves differently, and IMHO 
in a more correct way.

The following query returns NULL in PG:
SELECT NULL || 'fisk';

But in Oracle, it returns 'fisk':
SELECT NULL || 'fisk' FROM DUAL;

The latter seems more logical...

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andreas Joseph Krogh
On Wednesday 18 October 2006 14:15, Csaba Nagy wrote:
  The following query returns NULL in PG:
  SELECT NULL || 'fisk';
 
  But in Oracle, it returns 'fisk':
  SELECT NULL || 'fisk' FROM DUAL;
 
  The latter seems more logical...

 Why would it be more logical ?

How many times do you *really* want to get the not known answer here instead 
of 'fisk'? To put it another way: When will it be *wrong* to return 'fisk'?

 NULL means value not known.

I know.

 Concatenate value not known with 'fisk' - what's the logical answer?

 I would say the logical result is 'value not known'... if one of the
 components is not known, how can you know what is the result ?

That's like saying: SELECT sum(field) should return NULL(value not known) if 
some of the tuples are NULL, which is definitly not what you want.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andreas Joseph Krogh
On Wednesday 18 October 2006 15:13, Andrew Dunstan wrote:
 Andreas Joseph Krogh wrote:
  This has been been discussed before, but Oracle behaves differently, and
  IMHO in a more correct way.
 
  The following query returns NULL in PG:
  SELECT NULL || 'fisk';
 
  But in Oracle, it returns 'fisk':
  SELECT NULL || 'fisk' FROM DUAL;
 
  The latter seems more logical...

 When in doubt, consult the standard ... Oracle's treatment of NULL is
 known to violate the standard, IIRC. Your measure of correctness seems
 to be appears to me more logical, but ours is complies with the
 standard.

I know PG violates the standard in other places and core's favourite argument 
for doing so is the standard is braindead here, so we do it our way.

 In any case, why should null have a string value of '' any more than it
 should have a value of 'blurfl'?

 Your analogy elsewhere with aggregate functions like sum() is not
 relevant, as these are documented to ignore null values.

I'm not advocating that NULL should have a string-vaule of anything, just that 
the ||-operator shuld treat NULL as dont bother with it and proceed 
concatenation.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andreas Joseph Krogh
On Wednesday 18 October 2006 15:15, Mario Weilguni wrote:
 If you want this behaviour you will have to explicitly handle it with

 COALESCE().

 regards,
 Lukas

 True. But there's a point where oracle is really better here, they named
 coalesce nvl = a lot easier to type ;-)

They actually support COALESCE now and explicit JOINs too.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] bug or feature, || -operator and NULLs

2006-10-18 Thread Andreas Joseph Krogh
On Wednesday 18 October 2006 14:44, Mario Weilguni wrote:
 Am Mittwoch, 18. Oktober 2006 13:52 schrieb Andreas Joseph Krogh:
  This has been been discussed before, but Oracle behaves differently, and
  IMHO in a more correct way.
 
  The following query returns NULL in PG:
  SELECT NULL || 'fisk';
 
  But in Oracle, it returns 'fisk':
  SELECT NULL || 'fisk' FROM DUAL;
 
  The latter seems more logical...

 I've worked alot with oracle a few years ago and I agree, the feature is
 handy and makes sometimes life easier, but it's simply wrong. I heard a
 while ago that newer oracle versions changed this to sql - standard, is
 this true?

Oracle(10.1.0.4.0) still treats '' as NULL.

Why do these discussions always end in academic arguments over whats more 
logical then not? From a *user's* point of view I really would like it to 
treat the NULL operand of || as '', and obviously many other (at least 
Oracle) users tend to agree with me on that.

On Wednesday 18 October 2006 14:42, Csaba Nagy wrote:
 And it would really return null, if aggregates wouldn't ignore the NULL
 values altogether... the null values are skipped before they get into
 the summing. The same happens with count, if you specify a column it
 will only count the ones which are not null:

If aggregates ignore NULL one could argue that so shuld the ||-operator?

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


[HACKERS] Wrong list, sorry (was: lower() not working correctly...?)

2006-09-15 Thread Andreas Joseph Krogh
Sent to pgsql-sql now.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


[HACKERS] lower() not working correctly...?

2006-09-15 Thread Andreas Joseph Krogh
I have the following query:

select lower(firstname) || ' ' || lower(lastname) from person

firstname and lastname are VARCHAR

lower() returns NULL when firstname OR lastname is NULL, is this correct?

This is 8.2devel from 24.08.2006.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] pg_dump schema breakup

2006-08-18 Thread Andreas Joseph Krogh
On Friday 18 August 2006 18:52, Tom Lane wrote:
 Naz Gassiep [EMAIL PROTECTED] writes:
  I propose that two more be added:
  --tables-only
  --constraints-only

 This doesn't seem well-defined at all.  There are many objects in a
 database that are definitely neither tables nor constraints, and it's
 not very clear what things should be considered constraints either.

 I think what you may really be after is the stuff that should be loaded
 before inserting data and the stuff that should be loaded after, but
 the above are poor names for these concepts.

But it certainly would be nice to be able to dump all that stuff:-)

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] file-locking and postmaster.pid

2006-05-25 Thread Andreas Joseph Krogh
On Thursday 25 May 2006 14:35, korry wrote:
  That's not workable, unless you want to assume that nothing on the
  system except Postgres uses SysV semaphores.  Otherwise something else
  could randomly gobble up the semid you want to use.  I don't care very
  much for requiring a distinct semid to be hand-specified for each
  postmaster on a machine, either.

 Yeah, that does suck.  Ok, naming problems seem to make semaphores
 useless.

 I'm back to byte-range locking, but if NFS is important and is truly
 unreliable, then that's out too.

 I've never had locking problems on NFS (probably because we tell our
 users not to use NFS), but now that I think about it, SMB locking is
 very unreliable so Win32 would be an issue too.

What I don't get is why everybody think that because one solution doesn't fit 
all needs on all platforms(or NFS), it shouldn't be implemented on those 
platforms it *does* work on. Why can't those platforms(like Linux) benefit 
from a better solution, if one exists? There are plenty of examples of 
software providing better solutions on platforms supporting more features.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] file-locking and postmaster.pid

2006-05-24 Thread Andreas Joseph Krogh
On Tuesday 23 May 2006 19:36, Tom Lane wrote:
 Adis Nezirovic [EMAIL PROTECTED] writes:
  Well, maybe you could tweak postgres startup script, add check for post
  master (either 'pgrep postmaster' or 'ps -axu | grep [p]ostmaster'), and
  delete pid file on negative results.

 This is exactly what you should NOT do.

 A start script that thinks it is smarter than the postmaster is almost
 certainly wrong.  It is certainly dangerous, too, because auto-deleting
 that pidfile destroys the interlock against having two postmasters
 running in the same data directory (which WILL corrupt your data,
 quickly and irretrievably).  All it takes to cause a problem is to
 use the start script to start a postmaster, forgetting that you already
 have one running ...

My PG is not started with startup-scripts, but with this command:

pg_ctl -D $PGDATA -l $PGDIR/log/logfile-`date +%Y-%m-%d`.log start

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] file-locking and postmaster.pid

2006-05-24 Thread Andreas Joseph Krogh
On Wednesday 24 May 2006 11:36, Andreas Joseph Krogh wrote:
 On Tuesday 23 May 2006 19:36, Tom Lane wrote:
  Adis Nezirovic [EMAIL PROTECTED] writes:
   Well, maybe you could tweak postgres startup script, add check for post
   master (either 'pgrep postmaster' or 'ps -axu | grep [p]ostmaster'),
   and delete pid file on negative results.
 
  This is exactly what you should NOT do.
 
  A start script that thinks it is smarter than the postmaster is almost
  certainly wrong.  It is certainly dangerous, too, because auto-deleting
  that pidfile destroys the interlock against having two postmasters
  running in the same data directory (which WILL corrupt your data,
  quickly and irretrievably).  All it takes to cause a problem is to
  use the start script to start a postmaster, forgetting that you already
  have one running ...

 My PG is not started with startup-scripts, but with this command:

 pg_ctl -D $PGDATA -l $PGDIR/log/logfile-`date +%Y-%m-%d`.log start

... and manually after login, ie. not at boot-time.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] file-locking and postmaster.pid

2006-05-24 Thread Andreas Joseph Krogh
On Wednesday 24 May 2006 21:03, korry wrote:
  I'm sure there's a good reason for having it the way it is, having so
  many smart knowledgeable people working on this project. Could someone
  please explain the rationale of the current solution to me?

 We've ignored Andreas' original question.  Why not use a lock to
 indicate that the postmaster is still running?  At first blush, that
 seems more reliable than checking for a (possibly recycled) process ID.

As Tom replied: Portability.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] file-locking and postmaster.pid

2006-05-24 Thread Andreas Joseph Krogh
On Wednesday 24 May 2006 20:52, Andrej Ricnik-Bay wrote:
 On 5/24/06, Andreas Joseph Krogh [EMAIL PROTECTED] wrote:
   My PG is not started with startup-scripts, but with this command:
  
   pg_ctl -D $PGDATA -l $PGDIR/log/logfile-`date +%Y-%m-%d`.log start
 
  ... and manually after login, ie. not at boot-time.

 I'd suggest trying to fix your Linux-install instead of mucking
 about with Postgres, and this really a pgsql-novice question,
 not a -hackers thing.

I'm sorry, can't resist, but this has to be *the* dumbest reply to these sort 
of questions. What makes you think it *only* happens when linux freezes(btw, 
I suspect my NVIDIA-driver to be the problem on my laptop, not Linux itself). 
Still - PG *should* handle that situation too, it's like a power outage. I've 
been using Linux exclusively since '96 and PG since 6.5, so I don't consider 
myself a novice in neither. Why PG doesn't use locking *is* definitely 
a -hackers thing.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


[HACKERS] file-locking and postmaster.pid

2006-05-23 Thread Andreas Joseph Krogh
Hi all.

I've experienced several times that PG has died somehow and the postmaster.pid 
file still exists 'cause PG hasn't had the ability to delete it upon proper 
shutdown. Upon start-up, after such an incidence, PG tells me another PG is 
running and that I either have to shut down the other instance, or delete the 
postmaster.pid file if there really isn't an instance running. This seems 
totally unnecessary to me. Why doesn't PG use file-locking to tell if another 
PG is running or not? If PG holds an exclusive-lock on the pid-file and the 
process crashes, or shuts down, then the lock(which is process-based and 
controlled by the kernel) will be removed and another PG which tries to start 
up can detect that. Using the existence of the pid-file as the only evidence 
gives too many false positives IMO.

I'm sure there's a good reason for having it the way it is, having so many 
smart knowledgeable people working on this project. Could someone please 
explain the rationale of the current solution to me?

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [HACKERS] file-locking and postmaster.pid

2006-05-23 Thread Andreas Joseph Krogh
On Tuesday 23 May 2006 17:54, Tom Lane wrote:
 Andreas Joseph Krogh [EMAIL PROTECTED] writes:
  I've experienced several times that PG has died somehow and the
  postmaster.pid file still exists 'cause PG hasn't had the ability to
  delete it upon proper shutdown. Upon start-up, after such an incidence,
  PG tells me another PG is running and that I either have to shut down the
  other instance, or delete the postmaster.pid file if there really isn't
  an instance running. This seems totally unnecessary to me.

 The postmaster does check to see whether the PID mentioned in the file
 is still alive, so it's not that easy for the above to happen.  If you
 can provide details of a scenario where a failure is likely, we'd like
 to know about it.  Also, what PG version are you talking about?

I have experienced this with PG-8.1.3 and will provide details if I can make 
it happen. Basically it has happened when I have had to hard-reset my 
laptop due to some strange bugs in Linux which have made it hang.

  Why doesn't PG use file-locking to tell if another
  PG is running or not?

 Portability.

Ok.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] Running PostGre on DVD

2005-11-15 Thread Andreas Joseph Krogh
On Tuesday 15 November 2005 12:29 am, Jim C. Nasby wrote:
 Why do you need to run PostgreSQL as admin? There shouldn't be any need
 for this.

Actually I've run into a scenario where this was needed. I'm not a Windows 
expert, so there might be some way to get around this:

I have a localadmin account on the workstation(which is a member of a domain). 
As this localadmin(with full local administrative privileges) I created a 
local user postgres to run PostgreSQL as. The problem was that the policy 
for the domain the machine was a member of(which obviously overrides local 
settings) prevented this new local user to have local login privileges. 
Therefore I couldn't create a user to run the postmaster as. I was stuck 
with my admin-user, which I was not able to start PG as. This was quite 
frustrating as I really wanted to install Tomcat+PG to run a demo-webapp for 
a customer on one of their machines. There really should be an option for 
Yes, I really want to run PG as a user with Administrator-privileges on 
Windows. I promiss not to bug -hacker about any potential security-problems I 
might experience.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] Running PostGre on DVD

2005-11-15 Thread Andreas Joseph Krogh
On Tuesday 15 November 2005 02:07 pm, Martijn van Oosterhout wrote:
 On Tue, Nov 15, 2005 at 01:51:04PM +0100, Magnus Hagander wrote:
  Huh. The stated problem is that the low privilege account does *not*
  have the required privilege (to log in).
  Note that PostgreSQL doesn't really require log on locally for
  anything other than initdb. So if you can initdb on a different box and
  copy it there, or somehow get the permissions temporarily, the server
  will workf ine. The server only requires Log in as a service.

 Sorry, my understanding of Windows permissions is hazy at times. You
 have permission to create users, but not permission to run programs as
 the user you created (because you need to login). And there is a
 distinction between running as a service and running as a program(?!).

 So I think my statement is correct that the above user cannot run
 programs as anything other than administrator privelidges. Like you
 said, if he could, this discussion would be moot.

  If the security is set up so that you can use a local *admin* acconut
  but not a local *nonadmin* accuont, then your domain people really need
  to look over their security policies, because they are very very broken
  indeed.

 That was the way I read it and I agree, that's a very broken way to set
 things up.

 Have a nice day,

Broken or not, it's a setup I'm not in control over. And I'm certainly not the 
guy to hack the disable admin-security-check on windows feature:-(

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] Running PostGre on DVD

2005-11-15 Thread Andreas Joseph Krogh
On Tuesday 15 November 2005 02:16 pm, Pollard, Mike wrote:
  I explain myself about running PostGre as admin.
 
  In fact I don't want specifically run PostGre as admin. The problem

 is, on

  the computers the application including PostGre will run, I'm not sure
  that
  the user won't have any admin or power user rights. Furthermore, I've
  noticed that on certain domains, any user created is automatically

 added

  to
  a default group having power user rights (that is actually happening

 to

  me).

 To be honest, the fact that Postgres forces you to run as a non-admin
 user has given me nothing but headaches.  (yes, I know, the problem is
 defaulting everyone to admin rights is the problem.  But that's where I
 am).  I have been kicking around the idea of posting a change to allow
 you to run as admin, but in the meanwhile if you can build Postgres on
 your machine, the fix is very easy.  Go into src/backend/main/main.c and
 find the line

   if (pgwin32_is_admin())

 and change it to

   if (false  pgwin32_is_admin())

Thanks, I'll see if I can build PG on Windows now.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] Running PostGre on DVD

2005-11-15 Thread Andreas Joseph Krogh
On Tuesday 15 November 2005 03:05 pm, Magnus Hagander wrote:
   I explain myself about running PostGre as admin.
  
   In fact I don't want specifically run PostGre as admin. The problem
 
  is, on
 
   the computers the application including PostGre will run,
 
  I'm not sure
 
   that the user won't have any admin or power user rights.
 
  Furthermore,
 
   I've noticed that on certain domains, any user created is
   automatically
 
  added
 
   to
   a default group having power user rights (that is actually happening
 
  to
 
   me).
 
  To be honest, the fact that Postgres forces you to run as a
  non-admin user has given me nothing but headaches.  (yes, I
  know, the problem is defaulting everyone to admin rights is
  the problem.  But that's where I am).  I have been kicking
  around the idea of posting a change to allow you to run as
  admin,

 This has been proposed before, and always rejected. While you're always
 welcome to provide a patch, I'm very doubtful it would be accepted into
 the main product.

Oracle allows you to run it as admin... Don't know about SQL Server...
My bet is PG will some day bite the bullet and allow this too as more and more 
will use PG on Windows.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] Running PostGre on DVD

2005-11-15 Thread Andreas Joseph Krogh
On Tuesday 15 November 2005 03:37 pm, Tom Lane wrote:
 Magnus Hagander [EMAIL PROTECTED] writes:
  To be honest, the fact that Postgres forces you to run as a
  non-admin user has given me nothing but headaches.  (yes, I
  know, the problem is defaulting everyone to admin rights is
  the problem.  But that's where I am).  I have been kicking
  around the idea of posting a change to allow you to run as
  admin,
 
  This has been proposed before, and always rejected. While you're always
  welcome to provide a patch, I'm very doubtful it would be accepted into
  the main product.

 The example given in this thread certainly isn't going to change
 anybody's mind.  Hi, I propose reducing everybody's security because
 my local admins insist on an utterly brain-dead security policy.

Tom, nobody wants to reduce everybody's security, and nobody is proposing 
changes leading to such. I just believe more than me agree that having this 
as an option on Windows wouldn't hurt anybody, but would rather make life 
simpler for some Windows people. Anyway, I don't use Windows on a regular 
basis, so it's not that important to me...

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Hoffsveien 17   | know how to do a thing and to watch |
PO. Box 425 Skøyen  | somebody else doing it wrong, without   |
0213 Oslo   | comment.|
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] pgdump

2005-01-14 Thread Andreas Joseph Krogh
On Friday 14 January 2005 11:45, Enrico wrote:
 Is there anyone who written a patch for a multiple pg_dump like:

 pg_dump -t table1 table2 ... tableN dbname

Yes, I have such a patch lying around(pg_dump -t table1 -t table2 ... dbname).

It's for 7.4, but shouldn't be hard to port to 8.0.

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| Can i wash my clothes with my dvd drive?|
Hoffsveien 17   | Or do i need to replace it with a washing   |
PO. Box 425 Skøyen  | machine??   |
0213 Oslo   | |
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+


pgpEVfmcVbnTS.pgp
Description: PGP signature


Re: [HACKERS] pgdump

2005-01-14 Thread Andreas Joseph Krogh
On Friday 14 January 2005 14:54, Enrico wrote:
 Yes, I have such a patch lying around(pg_dump -t table1 -t table2 ...
  dbname).
 
 It's for 7.4, but shouldn't be hard to port to 8.0.

 Oh wonderful, how can I see that? I'm working with 7.4.x version.

Actually, it's for 7.4beta3, but should probably apply to 7.4 final as 
well

Here it is:

http://dev.officenet.no/~andreak/pg_dump.c.diff

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| Can i wash my clothes with my dvd drive?|
Hoffsveien 17   | Or do i need to replace it with a washing   |
PO. Box 425 Skøyen  | machine??   |
0213 Oslo   | |
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [HACKERS] Unexpected subquery behaviour

2004-07-26 Thread Andreas Joseph Krogh
On Tuesday 27 July 2004 01:15, Ian Barwick wrote:
 Apologies if this has been covered previously.

 Given a statement like this:
   SELECT * FROM foo WHERE id IN (SELECT id FROM bar)
 I would expect it to fail if bar does not have a column id. The
 test case below (tested in 7.4.3 and 7.4.1) shows this statement
 will however appear succeed, but produce a cartesian join (?) if bar
 contains a foreign key referencing foo.id.
[snip]
 test= SELECT * FROM foo WHERE id IN (SELECT id FROM bar);
  id
 
   1
   2
 (2 rows)

This, however, does not work:
andreak=# SELECT * FROM foo WHERE id IN (SELECT b.id FROM bar b);
ERROR:  column b.id does not exist

-- 
Andreas Joseph Krogh [EMAIL PROTECTED]
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| - a tool should do one job, and do it well. |
Hoffsveien 17   | |
PO. Box 425 Skøyen  | |
0213 Oslo   | |
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+


pgp69RNqmy7ba.pgp
Description: PGP signature


  1   2   >