Re: [GENERAL] Natural key woe

[Yeb Havinga]

On 13/05/14 11:44, Oliver Kohll - Mailing Lists wrote:

The problem came when someone entered a record with no subject, but left it 
null. When this was copied over and present in both tables, the *next* time the 
join was done, a duplicate was created because the join didn't see them as 
matching (null != null).

Maybe you can use x IS NOT DISTINCT FROM y ?

regards,
Yeb



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

Re: [GENERAL] Postgres as In-Memory Database?

[Yeb Havinga]

On 2014-04-01 04:20, Jeff Janes wrote:
On Sunday, March 30, 2014, Stefan Keller > wrote:


Hi Jeff

2013/11/20 Jeff Janes >


I don't know what you mean about enhancements in the buffer
pool.  For an in-memory database, there shouldn't be a buffer
pool in the first place, as it is *all* in memory.


You are right: In-memory DBs are making buffer-pooling obsolete -
except for making data persistent (see below).



I would be very reluctant to use any database engine which considered 
disk access obsolete.


The disk is not obsolete but something called 'anti-caching' is used:
http://www.vldb.org/pvldb/vol6/p1942-debrabant.pdf


Are there any show cases out there?


What did the HANA users have to say?  Seems like they would be in the 
best position to provide the test cases.


This paper provides some insights into the research behind HANA 
http://www.sigmod09.org/images/sigmod1ktp-plattner.pdf


regards
Yeb

Re: [GENERAL] v9.1.3 WITH with_query UPDATE

[Yeb Havinga]

On 2012-06-16 19:11, Bill House wrote:

Would someone please point me to (or supply) some working examples of
UPDATE commands using the WITH clause syntax as described in the manual
(pdf version page 1560) and referring to Section 7.8 (pdf version page 104)?


http://www.depesz.com/2011/03/16/waiting-for-9-1-writable-cte/

http://www.slideshare.net/davidfetter/writeable-ct-espgconmay2011

regards,
Yeb


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

Re: [GENERAL] Extension table data

[Yeb Havinga]

On 2012-06-09 08:56, Keith Fiske wrote:

Looking at the docs, I think the extension authors may have only had
configuration data in mind for extension tables. I don't see any
reason why we shouldn't be able to put any sort of table in our
extensions, some having actual data, not just config.


True. We've made several extensions that consist only of table data, 
such as contents of medical codesystems, where the actual terminology 
comes from another extension that has functions and empty codesystem 
tables. I remember there were some issues making it. IIRC the table data 
would not be dropped on extension drop.


regards,
Yeb

--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data


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

Re: [GENERAL] Download not found for SEPostgreSQL

[Yeb Havinga]

On 2012-04-10 09:40, John R Pierce wrote:

On 04/09/12 11:39 PM, Eye Gee wrote:
We would like to implement the security-enhanced postgresql 
(SEPostgreSQL ) on SUSE SP1. However we are unable to find the rpm 
packages.
Is SEPostgreSQL still available? We are using postgreSQL 9.1 and 
would like to use 9.1 version of SEPostgreSQL but the rpm download is 
not available.
Please provide me any links for download of SEPostgreSQL for SUSE 
SP1. Perhaps link for Red Hat too for evaluation.


http://code.google.com/p/sepgsql/

dunno if anyone is building RPMs, I kind of got the idea it was mostly 
experimental.   looks like its for PG 8.2 and 8.3 ?


sepgsql is merged in PG release 9.1, without row-level security. For the 
current status in the upcoming development release 9.1, see 
http://www.postgresql.org/docs/devel/static/sepgsql.html


regards,
Yeb


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

Re: [GENERAL] Recommendations for SSDs in production?

[Yeb Havinga]

On 2011-11-24 14:20, Yeb Havinga wrote:
 I really wonder at which point SSD life left will change to 99 on 
this drive..


Bingo! On the OCZ Vertex 2 PRO, SSD life left to 99 after just over 
100PB written.


230 Life_Curve_Status   0x0013   100   100   000Pre-fail  
Always   -   100
231 SSD_Life_Left   0x0013   099   099   010Pre-fail  
Always   -   0
232 Available_Reservd_Space 0x   000   000   000Old_age   
Offline  -   33
233 SandForce_Internal  0x   000   000   000Old_age   
Offline  -   22848
234 SandForce_Internal  0x0032   000   000   000Old_age   
Always   -   101952
235 SuperCap_Health 0x0033   100   100   002Pre-fail  
Always   -   0
241 Lifetime_Writes_GiB 0x0032   000   000   000Old_age   
Always   -   101952
242 Lifetime_Reads_GiB  0x0032   000   000   000Old_age   
Always   -   960


The Intel 710 now shows media wearout of 97.

226 Workld_Media_Wear_Indic 0x0032   100   100   000Old_age   
Always   -   3028
227 Workld_Host_Reads_Perc  0x0032   100   100   000Old_age   
Always   -   0
228 Workload_Minutes0x0032   100   100   000Old_age   
Always   -   22966
232 Available_Reservd_Space 0x0033   100   100   010Pre-fail  
Always   -   0
233 Media_Wearout_Indicator 0x0032   097   097   000Old_age   
Always   -   0
241 Host_Writes_32MiB   0x0032   100   100   000Old_age   
Always   -   3255685
242 Host_Reads_32MiB0x0032   100   100   000Old_age   
Always   -   22259


regards,
Yeb Havinga



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

Re: [GENERAL] Recommendations for SSDs in production?

[Yeb Havinga]

On 2011-11-04 16:24, David Boreham wrote:

On 11/4/2011 8:26 AM, Yeb Havinga wrote:


First, if your'e interested in doing a test like this yourself, I'm 
testing on ubuntu 11.10, but even though this is a brand new 
distribution, the smart database was a few months old. 
'update-smart-drivedb' had as effect that the names of the values 
turned into something useful: instead of #LBA's written, it now shows 
#32MiB's written. Also there are now three 'workload' related 
parameters.


I submitted the patch for these to smartmontools a few weeks ago and 
it is now in the current db but not yet in any of the distro update 
packages. I probably forgot to mention in my post here that you need 
the latest db for the 710. Also, if you pull the trunk source code and 
build it yourself it has the ability to decode the drive stats log 
data (example pasted below). I haven't yet found a use for this 
myself, but it does seem to have a little more informaiton than the 
SMART attributes. (Thanks to Christian Franke of the smartmontools 
project for implementing this feature)


Your figures from the workload wear roughly match mine. In production 
we don't expect to subject the drives to anything close to 100% of the 
pgbench workload (probably around 1/10 of that on average), so the 
predicted wear life of the drive is 10+ years in our estimates, under 
production loads.


The big question of course is can the drive's wearout estimate be 
trusted ? A little more information from Intel about how it is 
calculated would help allay concerns in this area.


TLDR: some numbers after three week media wear testing on a software 
mirror with intel 710 and ocz vertex 2 pro.


The last couple of weeks I've been running pgbench for an hour then 
sleep for 10 minutes in an infinite loop, just to see how values would grow.


This is the intel 710 mirror leg:

225 Host_Writes_32MiB   0x0032   100   100   000Old_age   
Always   -   3020093
226 Workld_Media_Wear_Indic 0x0032   100   100   000Old_age   
Always   -   2803
227 Workld_Host_Reads_Perc  0x0032   100   100   000Old_age   
Always   -   0
228 Workload_Minutes0x0032   100   100   000Old_age   
Always   -   21444
232 Available_Reservd_Space 0x0033   100   100   010Pre-fail  
Always   -   0
233 Media_Wearout_Indicator 0x0032   098   098   000Old_age   
Always   -   0
241 Host_Writes_32MiB   0x0032   100   100   000Old_age   
Always   -   3020093
242 Host_Reads_32MiB0x0032   100   100   000Old_age   
Always   -   22259


Note: raw value of 226 (E2) = 2803. According to 
http://www.tomshardware.com/reviews/ssd-710-enterprise-x25-e,3038-4.html 
you have to divide it by 1024 to get a percentage. That would be 2%. 
This matches with 098 of the (not raw) value at 233 (E9).


This is the ocz vertex 2 PRO mirror leg:

  5 Retired_Block_Count 0x0033   100   100   003Pre-fail  
Always   -   0
 12 Power_Cycle_Count   0x0032   100   100   000Old_age   
Always   -   22
100 Gigabytes_Erased0x0032   000   000   000Old_age   
Always   -   21120
170 Reserve_Block_Count 0x0032   000   000   000Old_age   
Always   -   34688
177 Wear_Range_Delta0x   000   000   000Old_age   
Offline  -   3
230 Life_Curve_Status   0x0013   100   100   000Pre-fail  
Always   -   100
231 SSD_Life_Left   0x0013   100   100   010Pre-fail  
Always   -   0
232 Available_Reservd_Space 0x   000   000   000Old_age   
Offline  -   33
233 SandForce_Internal  0x   000   000   000Old_age   
Offline  -   21184
234 SandForce_Internal  0x0032   000   000   000Old_age   
Always   -   94656
235 SuperCap_Health 0x0033   100   100   002Pre-fail  
Always   -   0
241 Lifetime_Writes_GiB 0x0032   000   000   000Old_age   
Always   -   94656
242 Lifetime_Reads_GiB  0x0032   000   000   000Old_age   
Always   -   960


Here the 177 (B1) wear range delta is on a raw value of 3 - this isn't 
ssd life left, but Delta between most-worn and least-worn Flash blocks. 
I really wonder at which point SSD life left will change to 99 on this 
drive..


regards,
Yeb Havinga



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

Re: [GENERAL] Recommendations for SSDs in production?

[Yeb Havinga]

On 2011-11-04 04:21, Kurt Buff wrote:
Oddly enough, Tom's Hardware has a review of the Intel offering today 
- might be worth your while to take a look at it. Kurt 


Thanks for that link! Seeing media wearout comparisons between 'consumer 
grade' and 'enterprise' disks was enough for me to stop thinking about 
the vertex 3 and intel 510 behind hardware raid: I'm going to stick with 
Intel 710 and Vertex 2 Pro on onboard SATA.


Tom's Hardware also showed how to test wearout using the workload 
indicator, so I thought lets do that with a pgbench workload.


First, if your'e interested in doing a test like this yourself, I'm 
testing on ubuntu 11.10, but even though this is a brand new 
distribution, the smart database was a few months old. 
'update-smart-drivedb' had as effect that the names of the values turned 
into something useful: instead of #LBA's written, it now shows #32MiB's 
written. Also there are now three 'workload' related parameters.


225 Host_Writes_32MiB   0x0032   100   100   000Old_age   
Always   -   108551
226 Workld_Media_Wear_Indic 0x0032   100   100   000Old_age   
Always   -   17
227 Workld_Host_Reads_Perc  0x0032   100   100   000Old_age   
Always   -   0
228 Workload_Minutes0x0032   100   100   000Old_age   
Always   -   211
232 Available_Reservd_Space 0x0033   100   100   010Pre-fail  
Always   -   0
233 Media_Wearout_Indicator 0x0032   100   100   000Old_age   
Always   -   0
241 Host_Writes_32MiB   0x0032   100   100   000Old_age   
Always   -   108551
242 Host_Reads_32MiB0x0032   100   100   000Old_age   
Always   -   21510


Tom's hardware on page 
http://www.tomshardware.com/reviews/ssd-710-enterprise-x25-e,3038-4.html 
shows how to turn these numbers into useful values.


The numbers above were taken 211 minutes after I cleared the workload 
values with smartctl -t vendor,0x40 /dev/sda. If you do that, the 
workload values become 0, then after a few minutes they all become 65535 
and not before 60 minutes of testing you'll see some useful values returned.


During the test, I did two one hour pgbench runs on a md raid1 with the 
intel 710 and vertex 2 pro, wal in ram.

pgbench -i -s 300 t (fits in ram)
pgbench -j 20 -c 20 -M prepared -T 3600 -l  t  (two times)

% mediawear by workload is Workld_Media_Wear_Indic / 1024
17/1024 = .0166015625 %

Lets turn this into # days. I take the most pessimistic number of 120 
minutes of actual pgbench testing, instead of the total minutes since 
workload reset of 211 minutes.

120/(17/1024/100)/60/24 = 501.9608599031 days

The Host_Reads_32MiB value was 91099 before the test, now it is at 108551.
(108551-91099)*32/1024 = 545 GB written during the test.

(108551-91099)*32/1024/1024/(17/1024/100) = 3208 TB before media wearout.

This number fits between Tom's hardware's calculated wearout numbers, 
7268 TB for sequential and 1437 TB for random load.


-- Yeb


PS: info on test setup
Model Number:   INTEL SSDSA2BZ100G3  Firmware Revision:  6PB10362
Model Number:   OCZ-VERTEX2 PRO  Firmware Revision:  1.35

partitions aligned on 512kB boundary.
workload on ~20GB software raid mirror (drives are 100GB).

Linux client46 3.0.0-12-generic #20-Ubuntu SMP Fri Oct 7 14:56:25 UTC 
2011 x86_64 x86_64 x86_64 GNU/Linux
PostgreSQL 9.2devel on x86_64-unknown-linux-gnu, compiled by gcc 
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit


/proc/sys/vm/dirty_background_bytes set to 17850

non standard parameters of pg are:
maintenance_work_mem = 1GB # pgtune wizard 2011-10-28
checkpoint_completion_target = 0.9 # pgtune wizard 2011-10-28
effective_cache_size = 16GB # pgtune wizard 2011-10-28
work_mem = 80MB # pgtune wizard 2011-10-28
wal_buffers = 8MB # pgtune wizard 2011-10-28
checkpoint_segments = 96
shared_buffers = 5632MB # pgtune wizard 2011-10-28
max_connections = 300 # pgtune wizard 2011-10-28

Latency and tps graphs of *one* of the 20 clients during the second 
pgbench test are here: http://imgur.com/a/jjl13 - note that max latency 
has dropped from ~ 3 seconds from earlier tests to ~ 1 second - this is 
mainly due to an increase of checkpoint segments from 16 to 96.





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

Re: [GENERAL] Recommendations for SSDs in production?

[Yeb Havinga]

On 2011-11-03 04:02, Benjamin Smith wrote:


Which is what we're trying next, X25E. 710's apparently have 1/5th the rated
write endurance, without much speed increase, so don't seem like such an
exciting product.


I've tested the 710 with diskchecker.pl and it doesn't lie about it's 
cache status. I'm note sure about the X25E. that might also be a factor 
in the equation. According to Greg Smith the X25E is not ok - 
http://www.2ndquadrant.com/static/2quad/media/pdfs/talks/bottom-up-benchmarking-2011.pdf


Also I'm *really* interested to know from one of the bad vertex 3's, 
what firmware is on it. (hdparm -I /dev/sdX or smartctl -i /dev/sdX).


-- Yeb


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

Re: [GENERAL] Recommendations for SSDs in production?

[Yeb Havinga]

On 2011-11-02 18:01, Benjamin Smith wrote:

So after months of using this SSD without any issues at all, we tentatively
rolled this out to production, and had blissful, sweet beauty until about 2
weeks ago, now we are running into sudden death scenarios.


Could you tell a bit more about the sudden death? Does the drive still 
respond to queries for smart attributes?


What firmware is on the Vertex 3? Anandtech talks about timing issues 
between certain IO controllers and the SandForce 2281 chipset, which 
appear to have been resolved in firmware 2.09 
(http://www.anandtech.com/show/4341/ocz-vertex-3-max-iops-patriot-wildfire-ssds-reviewed/1). 



regards,
Yeb



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

Re: [GENERAL] Inheritence issue scheme advice?

[Yeb Havinga]

On 2011-06-29 22:54, Casey Havenor wrote:

Problem:
  I like many other have come across the inherit issues.

I found the thread here about such issue...
http://postgresql.1045698.n5.nabble.com/FK-s-to-refer-to-rows-in-inheritance-child-td3287684.html

I grabbed the "fk_inheritance.v1.patch" file and have been trying to install
it for the last two hours. -- Got some help in hackers space so figured this
out.  BUT was it was recommended not to utilize a hacked version of
PostgreSQL.

For inheritance I'm using it for the following.  ONLY on/with UNIQUE
CONSTRAINTS and FOREIGN KEYS with OIDS enabled - which from my understanding
that shouldn't be an issues as there shouldn't any duplicate entries that
cause a deadlock?   -- So I would think this patch would be ok?

There are currently two caveats with the patch you mention

1: the user has to ensure global uniqueness of all pk's in an 
inheritance hierarchy. This is not a problem at all if e.g. your root 
relation has a pk with e.g. a default value from a sequence. The childs 
will inherit that default value from the same sequence.


2: the patch enables inserting rows in a relation that has a fk to a 
inheritance parent/root, with an fk key value that is found in one of 
the child relations of the refered relation. The patch as is fails to 
block deleting the referred record in the child relation: that check 
currently only works for referred records in actual relation the fk 
points to, not it's childs. It is not impossible to add this, but it 
just hasn't been programmed yet. So a trigger check to prevent these 
deletions also has to be made in user space.


I'd very much appreciate any feedback you have on the patch, if it 
matches your usecase.


regards,

--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data


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

Re: [GENERAL] SSDs with Postgresql?

[Yeb Havinga]

On 2011-04-28 21:34, Robert Treat wrote:


We have an open task to work on this same problem. What we had cobbled
together so far was some sql which converted the xlog value into an
integer (it's pretty ugly, but I could send it over if you think it
would help), which we could then stick in a monitoring system and
graph. To get an idea of traffic, I just multiplied this by 16MB. End
result ended up looking like this:
https://circonus.com/shared/graphs/9497d906-4c5b-e6d2-bf91-d8869e7c1668/OnxdZG

Couldn't decide on exactly where to go from there. That's graphing
MB/sec, which does map easily in my mind, since xlogs streams are in
16mb bursts. It would make more sense for wal streaming though (but in
that case we'd probably want to measure it more precisely).
If the goal is predicting the EOL of the SSD, graphing IO to the 
disk/partition, or perhaps graphing the smart values containing write 
cycles/GBs written/lifetime curve could work. Both monitoring disk IO 
(and iops) as well as smart values can be done with Symon: example 
picture with smart attributes graphed at http://i.imgur.com/T4NAq.png - 
the actual smart values for a SSD firmware would have to be configured 
though, since they vary a lot.


(*) http://www.xs4all.nl/~wpd/symon/

--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data


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

Re: [GENERAL] SSDs with Postgresql?

[Yeb Havinga]

On 2011-04-19 19:07, Benjamin Smith wrote:


On Sunday, April 17, 2011 01:55:02 AM Henry C. wrote:

>

> Exactly. Be aware of the risks, plan for failure and reap the rewards.


Just curious what your thoughts are with respect to buying SSDs and 
mirroring them with software RAID 1. (I use Linux/CentOS)




Since SSD fail when the write cycles are gone, it wouldn't make sense to 
buy two identical ones and put them in a RAID 1: under normal 
circumstances they'd fail simultanously. An idea I'm thinking of is to 
put both a OCZ Vertex 2 Pro and Intel 320 in a software RAID 1 setup. It 
would have the benefit that there are no complications to be expected 
with an extra device layer from a hardware RAID card, such as 
incompatibilites between controller and SSD firmware, or not being able 
to access the physical disk's smart values, which in the SSD case are 
important to figure out the remaining lifetime. Also since both drives 
have a supercap, PostgreSQL data on it would survive power failures. It 
would be interesting if md could be configured to do reads on both 
mirror legs and compare them before returning values, like a continual 
check, but there doesn't seem to be a option for something like that.


--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data

Re: [GENERAL] set theory question

[Yeb Havinga]

On 2011-02-03 18:41, Wappler, Robert wrote:

On 2011-02-02, matty jones wrote:


I am looking for a good book on the math and/or theory behind
relational databases and associated topics..  I am looking
some works on set theory, algebra, or any other books/papers
on the mechanics that databases are built on.  I found one
book online,

I can recommend "Foundations of Databases" (Abiteboul, Hull, Vianu)
http://www.amazon.com/Foundations-Databases-Logical-Serge-Abiteboul/dp/0
201537710
Definately recommended (note this book is also called the 'Alice' book 
because of it's front cover picture). It enabled me to write 
http://arxiv.org/PS_cache/arxiv/pdf/0705/0705.3949v1.pdf


regards,
Yeb Havinga



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

Re: [GENERAL] GiST indexing question

[Yeb Havinga]

On 2010-12-11 06:09, Greg Landrum wrote:

I'm now trying to add an equality operator (==) as follows:

CREATE OPERATOR == (
 LEFTARG = mol,
 RIGHTARG = mol,
 PROCEDURE = mol_eq,
 COMMUTATOR = '=',
 NEGATOR = '<>',
 RESTRICT = eqsel,
 JOIN = eqjoinsel
);

Are you sure the commutator and negator exist and are correct?


and I want to use this to extend the operator class:

CREATE OPERATOR CLASS mol_ops
DEFAULT FOR TYPE mol USING gist
AS
OPERATOR3   @>  (mol, mol),
OPERATOR4   <@ (mol, mol),
OPERATOR6   == (mol, mol),
 FUNCTION1   gmol_consistent (bytea, internal, int4),
 FUNCTION2   gmol_union (bytea, internal),
 FUNCTION3   gmol_compress (internal),
 FUNCTION4   gmol_decompress (internal),
 FUNCTION5   gmol_penalty (internal, internal, internal),
 FUNCTION6   gmol_picksplit (internal, internal),
 FUNCTION7   gmol_same (bytea, bytea, internal),
STORAGE bytea;

This seems to be ok.

But when I actually run a query I get an error:

moltest=# select count(*) from mols where m=='c1ncnnc1';
ERROR:  Unknown strategy: 6
I could not find that error exact message in the code, but something 
similar gave a hit.

$ grep -r 'Unknown strategy' *
$ grep -ri 'Unknown strategy' *
contrib/intarray/_int_gin.c:elog(ERROR, 
"ginint4_consistent: unknown strategy number: %d",
src/backend/access/gin/ginarrayproc.c:  elog(ERROR, 
"ginarrayconsistent: unknown strategy number: %d",
src/backend/access/gist/gistproc.c: elog(ERROR, 
"unknown strategy number: %d", strategy);
src/backend/access/gist/gistproc.c: elog(ERROR, 
"unknown strategy number: %d", strategy);


both the gistproc hits are in built-in consistent functions. This makes 
me believe that the error might be thrown in the rdkit's mol consistent 
function.



I guess I must be doing something stupid, but neither the docs nor the
internet have been particularly helpful in letting me know what.
Yeah, a hint might help for that error message. But since it doesn't, a 
quick way for problems like this is to increase log_error_verbosity to 
verbose, trigger the error again. Then the sourcefile and linenumber are 
logged as well. If it is then still unclear what's causing the error, 
you can use that information in gdb to set a breakpoint on that source 
location.

In case it's helpful, the full extension code (without the
non-functioning attempts at adding == to the index) is here:
http://rdkit.svn.sourceforge.net/viewvc/rdkit/trunk/Code/PgSQL/rdkit/
If you take a look at 
http://rdkit.svn.sourceforge.net/viewvc/rdkit/trunk/Code/PgSQL/rdkit/rdkit_gist.c?revision=1481&view=markup 
and then line 651: that's the error being thrown. You need to extend 
gmol_consistent to handle the strategy 6 number.


regards,
Yeb Havinga


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

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

[Yeb Havinga]

On 2010-11-17 02:55, Josh Berkus wrote:

If you do wish to have the data tossed out for no good reason every so
often, then there ought to be a separate attribute to control that.  I'm
really having trouble seeing how such behavior would be desirable enough
to ever have the server do it for you, on its terms rather than yours.

I don't quite follow you.  The purpose of unlogged tables is for data
which is disposable in the event of downtime; the classic example is the
a user_session_status table.
That sounds an awful lot like temporary tables. Perhaps the biggest 
problem of "unlogged tables" is that it doesn't connote "truncate at 
restart". With the truncate an unlogged table is more like a 'cluster 
temporary table'. While this is a very ugly name, I wonder if an DBA 
would expect a cluster temporary table to be backed up by default.


I just filled in the questionaire, and to my surprise I agreed more with 
the 'don't backup by default' question. The reason is that because the 
question also said: because it contains disposable data. Maybe a better 
question would have been: would you expect pg_dump to backup unlogged 
tables, at the point that you didn't more about them than that they are 
not written to the WAL? In that case I'd say: yes.


regards,
Yeb Havinga


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

Re: [GENERAL] Forcing the right queryplan

[Yeb Havinga]

Henk van Lingen wrote:

Now there are two types of query plans:

syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost, syslogtag, infounitid, message FROM systemevents WHERE (  ( to_tsvector('english', message) @@ to_tsquery ( '131.211.112.9')) )  ORDER BY id DESC LIMIT 100;   QUERY PLAN   
 
 Limit  (cost=0.00..10177.22 rows=100 width=159)

   ->  Index Scan Backward using systemevents_pkey on systemevents  (cost=0.00..
1052934.86 rows=10346 width=159)
 Filter: (to_tsvector('english'::regconfig, message) @@ to_tsquery('131.
211.112.9'::text))
(3 rows)

This one is useless (takes very long). However this one:
  

Hello Henk,

I saw your other mail today, I'm replying on this one for better formatting.

With a limit of 100 the planner guesses it will find 100 matching rows 
within some cost. At 500 rows the cost is higher than that of the second 
plan:

syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost, 
syslogtag, infounitid, message FROM systemevents WHERE (  ( 
to_tsvector('english', message) @@ to_tsquery ( '131.211.112.9')) )  ORDER BY 
id DESC LIMIT 500;
QUERY PLAN  
   


---
 Limit  (cost=40928.89..40930.14 rows=500 width=159)
   ->  Sort  (cost=40928.89..40954.76 rows=10346 width=159)
 Sort Key: id
 ->  Bitmap Heap Scan on systemevents  (cost=2898.06..40413.36 rows=1034
6 width=159)
   Recheck Cond: (to_tsvector('english'::regconfig, message) @@ to_t
squery('131.211.112.9'::text))
   ->  Bitmap Index Scan on msgs_idx  (cost=0.00..2895.47 rows=10346
 width=0)
 Index Cond: (to_tsvector('english'::regconfig, message) @@ 
to_tsquery('131.211.112.9'::text))

(7 rows)

works acceptable.
  
How to use the right plan regardless of the 'LIMIT-size'?
  
The planner obviously thinks it will have read 100 rows from 
systemevents backwards earlier than it actually does, with the where 
clause that contains the scanning for string 131.211.112.9. Increasing 
the stats target in this case will probably not help, since the 
statistics will not contain selectivity for all possible ts queries.


If the index is useless anyway, you might consider dropping it. 
Otherwise, increasing random_page_cost might help in choosing the 
otherplan, but on the other hand that plan has index scanning too, so 
I'm not to sure there.


If that doesn't help, it would be interesting to see some output of 
vmstat 1 (or better: iostat -xk 1) to see what is the bottleneck during 
execution of the first plan. If it is IO bound, you might want to 
increase RAM or add spindles for increased random io performance. If it 
is CPU bound, it is probably because of executing the to_tsvector 
function. In that case it might be interesting to see if changing 
ts_vectors cost (see ALTER FUNCTION ... COST .../ 
http://developer.postgresql.org/pgdocs/postgres/sql-alterfunction.html) 
again helps the planner to favor the second plan over the first.


regards,
Yeb Havinga


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

Re: [GENERAL] When is newly inserted data visible to another connection?

[Yeb Havinga]

fka...@googlemail.com wrote:

Hi all,

If there is nothing else wrong in our test case we noticed
the following:

We have done a test with two connections to the database on
different computers.  After the first client (writer) had
inserted new data into a quite simple table, it told another
client (by TCP communication) to be ready, however, this
second client (reader) did not see the data then immediately
in the database. So we delayed the reading client from 2 to
5s to have it see all data in the table.
  
Essential information is missing. Did the first client COMMIT before 
toggling client 2? Also you might find the information from 
http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html 
interesting, since that specifies how to control behaviour of concurrent 
transactions looking at each others data.

Secondly: If yes, is there a way to determine when newly
inserted data is visible to other clients?
  
Not before it is committed. To which clients the just committed data is 
visible depends on the transaction isolation level (see link above).


regards,
Yeb Havinga


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

Re: [GENERAL] Dynamic data model, locks and performance

[Yeb Havinga]

Pierre Thibault wrote:

Hello people of the Postgresql world!

I am wondering if Postgresql would a great choice for my database needs.

I would like to create a db with dynamic data model. I would like to 
be able to add tables and columns to existing tables while other 
queries are running.
It sounds a bit like the (commercial) ChronicDB add-on that was on the 
announce list a while ago, http://chronicdb.com/


regards,
Yeb Havinga


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

Re: [GENERAL] select a list of column values directly into an array

[Yeb Havinga]

A. Kretschmer wrote:

In response to Gauthier, Dave :
  

Is there a way to select a list of column values directly into an array?

 


create table foo (col1 text);

insert into foo (col1) values (?aaa?),(?bbb?),(?ccc?),(?ddd?),(?eee?);

 


I?d like to load up an array with {aaa,bbb,ccc,ddd,eee}, prefferably with a
single select statement.



test=*# select array_agg(col1) from foo;
   array_agg
---
 {aaa,bbb,ccc,ddd,eee}
  

/me scratches on head - wasn't there something with array?

select ARRAY (select col1 from foo);



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

Re: [GENERAL] select a list of column values directly into an array

[Yeb Havinga]

A. Kretschmer wrote:

In response to Gauthier, Dave :
  

Is there a way to select a list of column values directly into an array?

 


create table foo (col1 text);

insert into foo (col1) values (?aaa?),(?bbb?),(?ccc?),(?ddd?),(?eee?);

 


I?d like to load up an array with {aaa,bbb,ccc,ddd,eee}, prefferably with a
single select statement.



test=*# select array_agg(col1) from foo;
   array_agg
---
 {aaa,bbb,ccc,ddd,eee}
  

Or select ARRAY (select col1 from foo);



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

Re: [GENERAL] Bitmask trickiness

[Yeb Havinga]

Howard Rogers wrote:

insert into coloursample values (2,10,'Yellow and Orange');
  
But how do I find records which are ONLY yellow and orange

what about

select * from coloursample where colour = 10;

regards,
Yeb Havinga

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

Re: [GENERAL] consequent PQsendQueryPrepared() failed: another command is already in progress

[Yeb Havinga]

Anton Maksimenkov wrote:

I'm using libpq C Library. I prepared some query and trying to call it
many times.
But it success only at first time, and then fail with error:

... "another command is already in progress"
  
You are using the asynchronous queries while the testbed example program 
does not have a structure where an asynchronous loop is useful, and 
while doing that made an error probably with the consumeinput/isbusy 
duo. The problem will probably be gone when you switch to the 
synchronous libpq functions (for execute a prepared command)


If you really want the asynchronous api, I found the PQtrace facility 
very useful, to debug what's actually going on (or not).


regards,
Yeb Havinga


Here is my testbed:
int
main (register int const argc, register char *const argv[])
{
PGconn  *conn;
PGresult*res;

conn = PQsetdbLogin(PGHOST, PGPORT, PGOPTIONS, PGTTY, PGDBNAME,
PGLOGIN, PGPWD);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "PQstatus(): %s", PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
if ((res = PQprepare(conn, "GET_USER", "SELECT uid FROM users WHERE
uid = $1::INT LIMIT 1", 1, NULL)) == NULL) {
fprintf(stderr, "PQprepare() res == NULL");
PQfinish(conn);
exit(1);
}
if (PQresultStatus(res) != PGRES_COMMAND_OK) {
fprintf(stderr, "PQprepare() failed: %s", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
exit(1);
}

fprintf(stderr, "FIRST: ");
query(conn);

fprintf(stderr, "SECOND: ");
query(conn);

exit(0);
}

int
query(PGconn *conn)
{
const char *paramValues[1];
int paramLengths[1];
int paramFormats[1];
uint32_tbinaryIntVal;
PGresult   *res;

binaryIntVal = htonl((uint32_t) 15);
paramValues[0] = (char *) &binaryIntVal;
paramLengths[0] = sizeof(binaryIntVal);
paramFormats[0] = 1;

if (PQsendQueryPrepared(conn, "GET_USER", 1, paramValues,
paramLengths, paramFormats, 1) == 0) {
fprintf(stderr, "PQsendQueryPrepared() failed: %s", 
PQerrorMessage(conn));
return -1;
}
while (PQisBusy(conn))
if (PQconsumeInput(conn) == 0) {
fprintf(stderr, "PQconsumeInput() failed: %s", 
PQerrorMessage(conn));
return -1;
}

if ((res = PQgetResult(conn)) == NULL) {
fprintf(stderr, "PQgetResult() res == NULL");
PQfinish(conn);
return -1;
}
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
fprintf(stderr, "PQgetResult() failed: %s", 
PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return -1;
}

int i, uidFN;
char *uidPTR;
int uid;

uidFN = PQfnumber(res, "uid");
printf("tuples %d\n", PQntuples(res));
for (i = 0; i < PQntuples(res); i++) {
uidPTR = PQgetvalue(res, i, uidFN);
uid = ntohl(*((uint32_t *) uidPTR));
printf("tuple %d: uid[%d]\n", i, uid);
}
PQclear(res);

return 0;
}

$ ./test
FIRST: tuples 1
tuple 0: uid[15]
SECOND: PQsendQueryPrepared() failed: another command is already in progress

Where I was wrong?


And another question. Is it possible to simultaneously keep a number
of prepared queries and run any of them from time to time?
Something like this:
{
 PQprepare("Q1");
 PQprepare("Q2");
 PQprepare("Q3");
 ...
 query(Q1);
 ...
 query(Q2);
 ...
 query(Q1);
 ...
 query(Q3);
 ...
 query(Q2);
 ...
 query(Q3);
 ...
 query(Q1);
 ...
}
  



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

Re: [GENERAL] Is it possible to make the order of output the same as the order of input parameters?

[Yeb Havinga]

m. hvostinski wrote:
Thanks to all for the feedback. I keep getting impressed by how 
flexible PostgreSQL is.


Any ideas which query should perform better? I put together all the 
suggested approaches below.
EXPLAIN ANALYZE of these queries in psql should give hard numbers. My 
bet is on approach 3 to be the fastest since it does not require a join 
node and has shorter target list / memory to move around.


regards
Yeb Havinga


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

Re: [GENERAL] postgreSQL not working after upgrade to Ubuntu 10.4

[Yeb Havinga]

AllieH wrote:

Yesterday I upgraded from Ubuntu 9.10 to 10.4. Unfortunately this has caused
me serious problems with everything related to postgreSQL. It seems as
though the new version of ubuntu is requiring me to use PostgreSQL 8.4
instead of 8.3 which I was previously using. I tried installing 8.4 but I
can't seem to get the database to start. Has anyone had a similar problems?
  
Maybe this could have been prevented by 'holding' (in aptitude press '=' 
on the packages to hold at current level) the postgresql package at the 
current level.


Btw: it is possible to mix packages from multiple versions - you might 
be able to install the 8.3 again by adding "jaunty" back to 
/etc/apt/sources.list (not replace lucid but just add jaunty), run 
"apt-get update", then find the package "postgresql" in the aptitude 
tool, and press 'v': you then get all the versions available. Select the 
8.3 version, press '+' and 'g'. I just verified this on a test fresh 
10.4 installation and after 'g' I get no conflicts at all, however, 9.10 
(karmic) does not have postgres 8.4, but 9.04 (jaunty) does.


regards,
Yeb Havinga



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

Re: [GENERAL] GeSHi module for Postgresql?

[Yeb Havinga]

Leif Biberg Kristensen wrote:
Now I only need a list of the built-in functions. There doesn't seem to be a 
pg_get functions()?
  

Try psql -E postgres
(-E shows all queries psql does on the catalog)

then \df

regards,
Yeb Havinga


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

Re: [GENERAL] Help with tracking!

[Yeb Havinga]

Craig Ringer wrote:

Đỗ Ngọc Trí Cường wrote:
  

Dear all,

I've a problem but I search all the help file and can't find the solution.

I want to track all action of a specify role on all or one schema in
database.

Can you help me?



You can use statement-level logging, though there are no facilities in
statement-level logging to restrict what is logged to only one role's
activity.

You can use the usual audit triggers on database tables, which is what I
would recommend. Audit triggers in PostgreSQL cannot track reads
(SELECTs), only INSERT/UPDATE/DELETE and in 8.4 also TRUNCATE. They
cannot track ALTER/RENAME/DROP table, changes to sequences, etc. It is
trivial to write an audit trigger that only records anything when a user
is a member of a particular role.
  
Yes tracking SELECTs needs would have to go with a log file, since also 
a DO INSTEAD rule on SELECT has to be another SELECT command, and cannot 
e.g. be a INSERT followed by a SELECT.


Something similar is mentioned in this thread: 
http://archives.postgresql.org/pgsql-performance/2008-07/msg00144.php


regards,
Yeb Havinga


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

Re: [GENERAL] Complete row is fetched ?

[Yeb Havinga]

A. Kretschmer wrote:

In response to Satish Burnwal (sburnwal) :
  

I have a ques - say I have a table that has 10 columns. But in a simple
select query from that table, I use just 3 columns. I want to know
whether even for fetching 3 columns, read happens for all the 10 columns
and out of that the required 3 columns are returned ? ie Does the
complete row with all the 10 columns are fetched even though I need just
3 columns ? OR only 3 columns are fetched ?



Depends, large columns (TEXT, BYTA) are TOASTed¹, that means, the content
of this columns stored in a other table. So it is always a good idea to
specify all rows you needed and don't use select *.

¹ http://www.postgresql.org/docs/8.4/interactive/storage-toast.html
  
Another reason to prefer explicit column lists over SELECT * for queries 
on relations with a lot of columsn and rows, is that it enables some 
plan nodes to be more economical with memory, see e.g. comment of 
'disuse_physical_tlist':


/*
* disuse_physical_tlist
*  Switch a plan node back to emitting only Vars actually referenced.
*
* If the plan node immediately above a scan would prefer to get only
* needed Vars and not a physical tlist, it must call this routine to
* undo the decision made by use_physical_tlist().  Currently, Hash, Sort,
* and Material nodes want this, so they don't have to store useless columns.
*/

regards,
Yeb Havinga


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

Re: [GENERAL] [offtopic] How do you name a table...

[Yeb Havinga]

Ognjen Blagojevic wrote:

Hi,

How do you name a table which sole purpose is to store a list of 
values? E.g.


Is this:
a. Lookup table
b. Classifier
c. Cypher(er)?

d. valueset?

regards,
Yeb Havinga


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

Re: [GENERAL] Solid State Drives with PG

[Yeb Havinga]

Gordan Bobic wrote:

John R Pierce wrote:

all the enterprise SAN guys I've talked with say the Intel x25 drives 
are consumer junk, about the only thing they will use is STEC Zeus, 
and even then they mirror them.


A couple of points there.

1) Mirroring flash drives is a bit ill advised since flash has a 
rather predictable long-term wear-out failure point. It would make 
more sense to mirror with a mechanical disk and use the SSD for reads, 
with some clever firmware to buffer up the extra writes to the 
mechanical disk and return completed status as soon as the data has 
been committed to the faster flash disk.
Interesting, a few days ago I read something in the mdadm config about a 
config for mirroring over 'slower' links, and was waiting for a proper 
use case/excuse to go playing with it ;-) (looking up again)...


  -W, --write-mostly
 subsequent  devices  lists  in a --build, --create, or 
--add command will be flagged as 'write-mostly'.  This is valid for 
RAID1 only and means that
 the 'md' driver will avoid reading from these devices if 
at all possible.  This can be useful if mirroring over a slow link.


regards,
Yeb Havinga


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

Re: [GENERAL] database connections and presenting data on the web

[Yeb Havinga]

Geoffrey wrote:
We are trying to determine the best solution for a web based 
application. We have 13 databases (separate postmaster for each 
database) that we need to retrieve data from in order to produce the 
web page.  This data is changing on a regular basis.  Question is:


1. Do we:

for database in 1-13;do
connect database
retrieve data
disconnect database
done
display data

2. Or, do we have a common table for all databases that a daemon keeps 
updated and simply pull the data from that table?


The data that's being retrieved is changing literally by the minute.

The cgi code is perl.
3. Like 1 but with the use of a connection pooler like pgpool. Not sure 
if pgpool supports asynchronous queries, but that would help as well by 
pulling data from the 13 databases in parallel instead of serial: get 
the queries onto the 13 servers without waiting for results, then as 
soon as data is ready, get results and display.


regards,
Yeb Havinga


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

Re: [GENERAL] Sorting performance vs. MySQL

[Yeb Havinga]

Greg Stark wrote:


You can do \set FETCH_COUNT to have psql use a cursor automatically.
  
It seems like a big win in this case. What would be the downside of 
having a fetch_count set default in psql?


regards
Yeb Havinga





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

Re: [GENERAL] Sorting performance vs. MySQL

[Yeb Havinga]

Scott Marlowe wrote:

On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang  wrote:
  

I have the exact same table of data in both MySQL and Postgresql. In Postgresql:



Just wondering, are these on the same exact machine?

  
Just reading up on this interesting thread. WFIW, 2 years ago I and a 
collegue of mine did a hardware comparison of early Intel and AMD 
desktop quadcore processors to run postgres database, with most other 
parts comparable. The intel processor was 20 to 30 % faster in cpu 
operations to the (first generation) Phenom at almost everything, except 
at index creation. The test that the AMD finished in a few minutes, we 
had to stop on the Intel because it simply didn't finish. We double 
checked configuration settings and could not find explainable 
differences. I hesitate to post this information here, because its hard 
to believe that an actual big difference between the processors exists, 
and it more likely was something in our test setup. Still: the 
difference was *only* in index creation, which is kindoff like a qsort yes?


egards
Yeb Havinga





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

Re: [GENERAL] Trying to add a type modifier to user created type

[Yeb Havinga]

Carsten Kropf wrote:

Hi *,
I have the following problem:
I wanted to add a new type that supports modifiers (comparable to 
numeric/varchar). I succeeded in adding the type modifier functions to my new 
type. These methods are called and the modifier is set. However the modifiers 
are not applied here. I don't know exactly how to get the modifiers to be 
applied to my type. I looked into the source code of varchar/numeric and a 
function called the same name as the type is added in there that gets called 
with a value of the particular type and the typmod, respectively. But I don't 
know, how to register this function properly in order to get called, when sth 
is to be stored in the database.
column. How can I achieve this/What do I have to do to get it to work?
  


It's been a while but I still remember exactly the same question 
(frustration?) from some while ago (maybe it is something for the docs)


You need to add a cast from the type to itself, e.g.

-- typmod coercion
CREATE CAST (complex AS complex)
WITH FUNCTION complextypmod(complex,int4)
AS IMPLICIT;

Where complextypemod would be the typmod apply functions (int4 is the 
typmod)


Regards,
Yeb Havinga


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

Re: [GENERAL] Orafce concat operator

[Yeb Havinga]

Pavel Stehule wrote:

What about adding something like operator ||| in the orafce package for
concat?



no, it could be confusing and it isn't enough, because it isn't only
|| or concat problem. On Oracle empty string is equal to NULL and NULL
is equal to empty string.

example: '' is null, length('')

http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/

so we are not able emulate this behave.
  

Hi Pavel,

Thanks for your quick reply. What you say is entirely true, however I'm 
not sure if you completely understood my question, so please do not be 
offended if I try to elaborate it a bit:


I did not ask for full '' = NULL emulation, only for an operator to 
match the concat function, so that code conversion with repetetive 
concats are easier (string || string || string etc) which occur 
frequently. Ofcourse a translator can be made that converst that to 
concat(string,concat(string,concat(string etc))), however that's 
confusing too in a different way. So imho the only problem is to think 
of an operator that somehow resembles || so the programmer recognizes a 
concat, but also adds something so the programmer recognizes: not 
strict. What about ||+ ?


And then, at the conversion of e.g. (string || string || string) IS 
NULL, confusion arises, but this is not due to the concat, but more to 
the IS NULL clause together with the strange '' = null, and that must be 
handled otherwise.


So the operator would speed up part of the code conversion.

regards,
Yeb Havinga




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

[GENERAL] Orafce concat operator

[Yeb Havinga]

Hello list,

The Orafce compatibility package doesn't seem to have operators defined 
(looked in the sql load file). The function I'm specifically interested 
in, is Oracle's concatenation that regards a NULL as the empty string 
and hence returns 'the other value'. This in contrast with Pg's || that 
returns NULL if either of the operands is NULL. The Orafce package 
contains a concat function with Oracle behaviour, however an operator is 
missing.


Having an associative operator has benefits over having only a function, 
since that would make translating expressions like 'monkey' || 'nut' || 
NULL easy.


What about adding something like operator ||| in the orafce package for 
concat?


Regards,
Yeb Havinga


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

Re: [GENERAL] Extending SQL in C using VARIABLE length type

[Yeb Havinga]

Carsten Kropf wrote:

Oh, I see, does the VARSIZE length field have to be the total number of bytes occupied 
(including VARHDRSZ and the size of the structure) or only the size that is used by 
"my" datatype?

Yes

 Then it would become pretty much obvious, why this is not supposed to work.
I'll try it out then.
  
My €0,02: rename the dimensions to vl_len_ to avoid confusion and get 
compiler errors where you now use 'dimension'. Add a macro that converts 
a pointnd structure to dimension int by taking the VARSIZE_ANY_EXHDR / 
sizeof(float8) and use it where dimension is used now. Or if your 
database is small you could keep dimension in the structure.



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

Re: [GENERAL] Extending SQL in C using VARIABLE length type

[Yeb Havinga]

Carsten Kropf wrote:
Actually, I thought, I did this using the int32 variable called 
"dimension" which should be exactly this field.

yes.

in = (PointND *) palloc(sizeof(float8) * dimensions + VARHDRSZ);
SET_VARSIZE(in, dimensions);

What about

len = sizeof(float8) * dimensions + VARHDRSZ;
in = (PointND *) palloc0(len);
SET_VARSIZE(in, len);



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

Re: [GENERAL] Extending SQL in C using VARIABLE length type

[Yeb Havinga]

Carsten Kropf wrote:
The usage of static types with fixed length was actually no problem 
for me, so I proceeded to variable length types.
I created an n-dimensional point structure called "PointND" that 
contains a field of float8 values of dynamic length. I also put in a 
int4/int32 field for the length specification, as required by the 
documentation. So the structure looks like the following:

struct PointND
{
int32 dimensions;
float8 coordinates[1];
};
The structure should begin with a int32 vl_len_ header. At creation / 
palloc time the size must be set with the SET_VARSIZE macro, and the 
size can be queried with the VARSIZE_ * macros -> doxygen.postgresql.org 
is your friend here. Take a look at e.g. contrib/cube for examples.


regards,
Yeb Havinga


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

Re: [GENERAL] Can LISTEN/NOTIFY deal with more than 100 every second?

[Yeb Havinga]

Gavin Mu wrote:

CREATE OR REPLACE RULE send_notify AS ON INSERT TO log DO ALSO NOTIFY logevent;
  

..

when I use 3 similar programs to feed data, which means about 75
events every second, I found that Postgres didn't send NOTIFY
opportunely, since the client do SELECT query every several hundreds
seconds, which is too long to be acceptable.
  

Hello Gavin,

The following might help from the notify docs:

"NOTIFY behaves like Unix signals in one important respect: if the same
notification name is signaled multiple times in quick succession,
recipients might get only one notification event for several executions
of NOTIFY."

So if your notify for instance could also add a unique number to the
notification name, then it will probably work as expected.

Regards,
Yeb Havinga




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

Re: [GENERAL] Can LISTEN/NOTIFY deal with more than 100 every second?

[Yeb Havinga]

Gavin Mu wrote:

CREATE OR REPLACE RULE send_notify AS ON INSERT TO log DO ALSO NOTIFY logevent;
  

..

when I use 3 similar programs to feed data, which means about 75
events every second, I found that Postgres didn't send NOTIFY
opportunely, since the client do SELECT query every several hundreds
seconds, which is too long to be acceptable.
  

Hello Gavin,

The following might help from the notify docs:

"NOTIFY behaves like Unix signals in one important respect: if the same 
notification name is signaled multiple times in quick succession, 
recipients might get only one notification event for several executions 
of NOTIFY."


So if your notify for instance could also add a unique number to the 
notification name, then it will probably work as expected.


Regards,
Yeb Havinga



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