Re: [GENERAL] new rows based on existing rows
On 3 May 2012, at 24:00, Tom Lane wrote: > Andy Chambers writes: >> So ideally, I'd like to be able to do > >> insert into foo (a,b,foo_date) >> select a,b,now() from foo old where >> returning oid, old.oid > >> ...but this doesn't work. It seems you only have access to the table >> being modified in a returning clause. > > For the moment I think all you could really do is what somebody else > suggested, namely eat the overhead of having an "old_id" column in > the table so that you can insert the value you want into that column, > thus making it available to the RETURNING clause. I was wondering, would an updatable view with a "pseudo-column" for the old_id do it? Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- 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] SQLSTATE XX000 Internal Error 7
G'day Tom, On 03/05/2012, at 11:57 AM, Tom Lane wrote: > Ben Madin writes: >> SQLSTATE[XX000]: Internal error: 7 ERROR: could not open file >> "base/102979/430122_fsm": Invalid argument > > [ scratches head ... ] AFAICS the only documented reason for open() to > fail with EINVAL on OS X is > > [EINVAL] The value of oflag is not valid. > > which is surely bogus since that code path calls it with a constant > value for oflag --- there's no way it could fail just some of the time. > > So this is smelling like a kernel or filesystem bug. I wonder exactly > which OS X update you're running, and what sort of filesystem the > database is stored on. I think that sounds bad! The OSX Update is 10.7.3 (11D50) The System is a 2.66 GHz Intel Core i7 with 8GB RAM. The database is stored on a partition that looks like : Capacity: 447.69 GB (447 687 770 112 bytes) Available:74.96 GB (74 956 308 480 bytes) Writable: Yes File System: Journaled HFS+ BSD Name: disk0s2 Mount Point: / Content: Apple_HFS and the data is stored in the /usr/local/pgsql-9.1/data directory, but there is a symlink (as I've retained the previous versions when I upgrade.) and so the /usr/local directory looks like : lrwxr-xr-x 1 root wheel 9 1 May 11:11 pgsql -> pgsql-9.1 drwxr-xr-x 11 root wheel 374 17 Feb 21:26 pgsql-8.4 drwxr-xr-x 8 root admin 272 17 Feb 21:26 pgsql-9.0 drwxr-xr-x 8 root admin 272 17 Feb 22:41 pgsql-9.1 and the data directory : drwx-- 20 _postgres _postgres 680 1 May 11:11 data is this the sort of exact information you were wondering? Since I last posted, I have again received : PL/pgSQL function "fill_ctybnda" line 18 at EXECUTE statement ERROR: could not open file "base/102979/430320_fsm": Invalid argument and I went looking and found in the base/102979/ directory: -rw---1 _postgres _postgres1253376 3 May 11:51 430320 -rw---1 _postgres _postgres 24576 3 May 11:51 430320_fsm so it look to my uneducated eye as though it has been able to open the file(quite a few of the files ending in _fsm have 24576 bytes) (PS How did you come to deciding that it was EINVAL - is that 'Error INVALid argument'?) cheers Ben -- 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] SQLSTATE XX000 Internal Error 7
Ben Madin writes: > SQLSTATE[XX000]: Internal error: 7 ERROR: could not open file > "base/102979/430122_fsm": Invalid argument [ scratches head ... ] AFAICS the only documented reason for open() to fail with EINVAL on OS X is [EINVAL] The value of oflag is not valid. which is surely bogus since that code path calls it with a constant value for oflag --- there's no way it could fail just some of the time. So this is smelling like a kernel or filesystem bug. I wonder exactly which OS X update you're running, and what sort of filesystem the database is stored on. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] logging query result size?
I'm not seeing anything in the docs, but is there a way in 9.1 to log the size of the query result in the same way that we can log the duration of the query? -- 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] spanish locale question
Hi there, Let me expand the collate situation. I´m from Perú and I have turned everything in postgresql.conf as 'es_PE.UTF-8' even the default_text_search_config = 'pg_catalog.spanish'. Even my Ubuntu 12.04 works in English I have es_PE locale too. In Spanish vowels as "u" is equal as "ú" or even "ü". So let me build an example: CREATE TABLE pru(id integer PRIMARY KEY, dad text, mum text, name text); INSERT INTO pru VALUES (1, 'león','valencia', 'josé'), (2, 'leon', 'mendoza', 'juan'), (3, 'león', 'valárd', 'jose'), (4, 'león','válencia', 'jos'), (5, 'león', 'mendoza', 'jua'), (6, 'leon', 'valencia', 'josie'), (7, 'león', 'valencia', 'josie'); if I do SELECT * FROM pru order by dad,mum,name; I get: id | dad | mum | name +--+--+--- 2 | leon | mendoza | juan 6 | leon | valencia | josie 5 | león | mendoza | jua 3 | león | valárd | jose 1 | león | valencia | josé 7 | león | valencia | josie 4 | león | válencia | jos (7 rows) Which is a wrong order (collation) in Spanish and I don´t understand why. But, I noticed that if I do: SELECT * FROM pru order by dad || mum || name; I get the correct order: id | dad | mum | name +--+--+--- 5 | león | mendoza | jua 2 | leon | mendoza | juan 3 | león | valárd | jose 4 | león | válencia | jos 1 | león | valencia | josé 6 | leon | valencia | josie 7 | león | valencia | josie (7 rows) Is this the correct way to order in Postgresql and if it´s not Does anyone have an idea and could please explain it to me? Thanks in advance. Tulio -- View this message in context: http://postgresql.1045698.n5.nabble.com/spanish-locale-question-tp5650043p5681819.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql log indicate too many execute S_2:COMMIT
my postgres version is 9.1.2.my machine is dell R710.3000 sessions connect to database concurrently,then doing some insert,delete,update. your meaning my database bottleneck is disk io slowly.i use local disk.i know transaction log need to wirte to disk. thanks.i will monitor it. -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-log-indicate-too-many-execute-S-2-COMMIT-tp5672225p5682141.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is there away to output a time stamp in a specified time zone with the time zone indicator (e.g. EDT)
On 05/02/2012 03:59 PM, Matrix Guy wrote: I'm referring to US time zones here. I'd like to output a time stamp field in a specified time zone. I'd also like to display the time zone (e.g. EDT) at the end. Ideally, this output needs to adjust the numeric portions of the stamp for standard time vs daylight time and then display EST or EDT accordingly. The switch between standard time and daylight time occurred on 3-11-2012. So time stamps before 3-11-2012 should have EST at the end and those after should have EDT. to_char(my_timestamp_field , 'MM/DD/ HH12:MI AM TZ') will output: 03/12/2012 10:23 AM EDT to_char(my_timestamp_fieldat time zone 'cdt', 'MM/DD/ HH12:MI AM TZ') will only output 03/12/2012 09:23 AM Because we'll be reporting events in multiple US time zones, we must let users know which time zone the output timestamp is in. How are you storing the timestamps? In other words in a timestamp with timezone field or not? Thanks, Alex -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SQLSTATE XX000 Internal Error 7
G'day all,
I'm going to go slowly on this, but I am intermittently (as in sometimes the
query works fine, sometimes it stops after 5 minutes and I get the message)
receiving the error message below on a long running query that is populating a
newly created table with a PostGIS Geometry column.
The Error Message is :
SQLSTATE[XX000]: Internal error: 7 ERROR: could not open file
"base/102979/430122_fsm": Invalid argument
I don't seem to be able to leverage any search engine to explain what this
message means - To many quotes, colons and brackets for google. I'm not even
sure why a file is being opened, but I assume that the file is part of the data
storage - which maybe I need to know about, but I haven't so far!
I have also fiddled a bit with the postgresql.conf settings to increase work
men etc. The details of the table and query are below.
So my question is really - what does this error message mean, and where do I
start looking for what could be causing it. Should I try a debug trace, or just
looking in the logs (I've attached the log entries at the bottom, but maybe I
should up the logging level)?
I haven't yet posted this to the postgis list, as it looked as though this
message is a postgresql message, not a postgis one.
cheers
Ben
I'm running on :
PostgreSQL 9.1.3 on x86_64-apple-darwin11.3.0, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658)
(LLVM build 2336.9.00), 64-bit
and PostGIS :
POSTGIS="1.5.3" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.7.1, 23 September 2009"
LIBXML="2.7.3" USE_STATS
The table definition at the time that the error is occurring is:
Table "system.ctybnda2009"
Column |Type | Modifiers
---+-+
recordid | integer | not null default
nextval('ctybnda2009_recordid_seq'::regclass)
ccode | character varying(3)|
year | integer | not null default 2005
fips | character varying(2)|
l_1_name | character varying |
l_2_name | character varying |
l_3_name | character varying |
area | numeric |
modfiedon | timestamp without time zone | not null default now()
the_geom | geometry|
Indexes:
"ctybnda2009_recordid_key" UNIQUE CONSTRAINT, btree (recordid)
Check constraints:
"enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (st_srid(the_geom) = 4326)
The query that causes the problem is :
INSERT INTO system.ctybnda2009 (ccode, the_geom)
SELECT m.country, st_multi(st_union(m.geom))
FROM maptable m
LEFT OUTER JOIN countries c
ON m.country = c.ccode
WHERE geom IS NOT NULL
AND m.valstart <= 2009
AND (m.valend IS NULL OR m.valend >= 2009)
GROUP BY 1
ORDER BY 1;
postgresql.log
2012-05-03 05:18:23 WSTERROR: could not open file "base/102979/430122_fsm":
Invalid argument
2012-05-03 05:18:23 WSTSTATEMENT: INSERT INTO system.ctybnda2011 (ccode,
the_geom) SELECT m.country, st_multi(st_union(m.geom)) FROM maptable m LEFT
OUTER JOIN countries c ON m.country = c.ccode WHERE geom IS NOT NULL AND
m.valstart <= $1 AND (m.valend IS NULL OR m.valend >= $2) GROUP BY 1 ORDER BY
1;
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advisory Locks and Prepared Transactions
[ sorry for slow response ] Stephen Rees writes: > Code that works on Pg8.3 raises an error on Pg9.1, is this a bug? Yes, it sure looks that way. Somebody decided they could mark userlocks as transactional, which was probably a bad idea altogether, and certainly wasn't followed up on adequately. regards, tom lane -- 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] Limit the normal user to see system catalog or not??? And create privilege???
On Wed, May 02, 2012 at 04:03:01PM -0700, Adrian Klaver wrote: > On 05/02/2012 11:42 AM, Bruce Momjian wrote: > > On Wed, Mar 28, 2012 at 01:54:58PM -0700, Adrian Klaver wrote: > >> On 03/28/2012 09:54 AM, leaf_yxj wrote: > >>> For oracle, the normal user can't see all the system catalog. but for > >>> postgresql, it looks like all the user can see the system catalog. Should > >>> we limit the user read privilege to system catalog? > >>> > >>> In oracle, the system privilege has create table, create view,create > >>> function. For postgresql database, how to control the user who only can > >>> create table but can't create view. Based on the test I did, once the user > >>> has the create privilege on the schema, the user will have any create > >>> privilege on that schema. In postgresql, Rule is used to control that ??? > >>> very confused! > >> > >> Path to unconfusion:): > >> http://www.postgresql.org/docs/9.0/interactive/sql-grant.html > >> > >> You can grant CREATE on a schema and then restrict CREATE within the > >> schema for different objects types. In recent versions you are > >> looking for ALL * IN SCHEMA schema_name where * is the object type. > > > > I think the problem with ALL * IN SCHEMA it just applies permissions on > > all objects in the schema at a point in time, i.e. it doesn't apply to > > objects created _after_ that command was run. > > True, but in the above was an explanation of default privileges which > led to this link: > > http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html > > ALTER DEFAULT PRIVILEGES does allow you to control what happens in the future. > Admittedly not the most obvious connection:) Oh, I forgot about that one. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Re: How to insert random character data into tables for testing purpose. THanks.
On Wed, May 02, 2012 at 11:03:47AM -0700, leaf_yxj wrote:
> Hi Andreas,
>
> Thanks a lot.
>
> I tried those following , they work.
>
> 1) insert into test values ( generate_series(1,1000),
> md5(random()::text));
>
>
> 2) create table t_random as select s, md5(random()::text) from
> generate_Series(1,5) s;
Here is a method that generates strings with a little more variability:
SELECT
(
SELECT string_agg(x, '')
FROM (
SELECT chr(ascii('A') + (random() * 25)::integer)
-- reference 'b' so it is correlated and re-evaluated
FROM generate_series(1, 40 + b * 0)
) AS y(x)
)
FROM generate_series(1,5) as a(b);
string_agg
--
JWNRUIEPJQGDVBXDJDTROFSNMKGVNAQWWEORMSHV
KNYGOWJXBEPOJICURIQGIIDFMRXYLFUUSTCIRLGR
PVHXEITVQKBZREFEVDDHHYCASEIKPOCFJJDNRWJJ
HKIYONDLQGSXFUXXZGHPUKUESXRLUATBLCAAPDLG
JEENYCLYISODDIGWGXHBYCCJECJTFUAOYECXGNWO
(5 rows)
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] Limit the normal user to see system catalog or not??? And create privilege???
On 05/02/2012 11:42 AM, Bruce Momjian wrote: > On Wed, Mar 28, 2012 at 01:54:58PM -0700, Adrian Klaver wrote: >> On 03/28/2012 09:54 AM, leaf_yxj wrote: >>> For oracle, the normal user can't see all the system catalog. but for >>> postgresql, it looks like all the user can see the system catalog. Should >>> we limit the user read privilege to system catalog? >>> >>> In oracle, the system privilege has create table, create view,create >>> function. For postgresql database, how to control the user who only can >>> create table but can't create view. Based on the test I did, once the user >>> has the create privilege on the schema, the user will have any create >>> privilege on that schema. In postgresql, Rule is used to control that ??? >>> very confused! >> >> Path to unconfusion:): >> http://www.postgresql.org/docs/9.0/interactive/sql-grant.html >> >> You can grant CREATE on a schema and then restrict CREATE within the >> schema for different objects types. In recent versions you are >> looking for ALL * IN SCHEMA schema_name where * is the object type. > > I think the problem with ALL * IN SCHEMA it just applies permissions on > all objects in the schema at a point in time, i.e. it doesn't apply to > objects created _after_ that command was run. True, but in the above was an explanation of default privileges which led to this link: http://www.postgresql.org/docs/9.0/interactive/sql-alterdefaultprivileges.html ALTER DEFAULT PRIVILEGES does allow you to control what happens in the future. Admittedly not the most obvious connection:) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there away to output a time stamp in a specified time zone with the time zone indicator (e.g. EDT)
I'm referring to US time zones here. I'd like to output a time stamp field in a specified time zone. I'd also like to display the time zone (e.g. EDT) at the end. Ideally, this output needs to adjust the numeric portions of the stamp for standard time vs daylight time and then display EST or EDT accordingly. The switch between standard time and daylight time occurred on 3-11-2012. So time stamps before 3-11-2012 should have EST at the end and those after should have EDT. to_char(my_timestamp_field , 'MM/DD/ HH12:MI AM TZ') will output: 03/12/2012 10:23 AM EDT to_char(my_timestamp_fieldat time zone 'cdt', 'MM/DD/ HH12:MI AM TZ') will only output 03/12/2012 09:23 AM Because we'll be reporting events in multiple US time zones, we must let users know which time zone the output timestamp is in. Thanks, Alex
Re: [GENERAL] new rows based on existing rows
Andy Chambers writes: > So ideally, I'd like to be able to do > insert into foo (a,b,foo_date) > select a,b,now() from foo old where > returning oid, old.oid > ...but this doesn't work. It seems you only have access to the table > being modified in a returning clause. Hm ... it is kind of annoying that that doesn't work, seeing that comparable locutions do work in UPDATE ... FROM ... RETURNING and DELETE ... USING ... RETURNING. And I think that the engine could actually do it easily enough. The problem is more of a SQL standards theoretic one: a sub-SELECT doesn't expose anything beyond its result columns. So the fact that foo might have some other columns besides what you selected for use in the INSERT isn't visible from "outside" the sub-SELECT. I don't see any way to fix this directly (ie, letting RETURNING "look inside" the sub-SELECT) that wouldn't be a horrid violation of both the letter and spirit of the SQL standard, not to mention logically inconsistent in assorted ways. Possibly we could allow the sub-SELECT to return more columns than the INSERT needs, but that seems like a pretty error-prone "feature". For the moment I think all you could really do is what somebody else suggested, namely eat the overhead of having an "old_id" column in the table so that you can insert the value you want into that column, thus making it available to the RETURNING clause. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pqlib garbage collection
Hi, regarding pqlib, from what I gather it is with garbage collection supported, yes? If so, is PQclear a left-over or still mandatory for avoiding leaks? Thanks Alex -- 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] PLR Help
Joe, Thats IT Thanks so much. I can't believe I missed that but I have gone crazy trying to figure that out and all that time I was just missing the "/usr/lib/R/lib/" in the ld.so.conf . Once I put that in and ran ldconfig, all was happy. Thanks so much. side note.. I did spend a little while looking for lib.R.so and wondering how I could be missing that file.. then I saw your follow-up email. Thanks again, Daniel On Wed, May 2, 2012 at 3:07 PM, Joe Conway wrote: > On 05/02/2012 10:59 AM, Joe Conway wrote: > > First, go find lib.R.so. Assuming it is in the above location, place > ^-- oops, I meant libR.so > > Joe > > -- > Joe Conway > credativ LLC: http://www.credativ.us > Linux, PostgreSQL, and general Open Source > Training, Service, Consulting, & 24x7 Support >
Re: [GENERAL] Difference between speed of 2 functions: SQL+STABLE and PLPGSQL+EXECUTE
Albe, thanks for detailed explaination.
But it's quite strange that SQL+STABLE function does not recalculate the
plan each time it is called. Because when I use a bunch of SQL+STABLE
functions in e.g. a sub-select of a complex query, I see in the plan of
this complex queries that function calls are "expanded". It looks like
PostgreSQL uses SQL code defined in SQL+STABLE functions and merges
(injects) it into the main query instead of the function call.
E.g.:
==
CREATE TABLE a(i INTEGER, t VARCHAR(5));
INSERT INTO a(i, t) SELECT s, '' FROM generate_series(1, 1) s;
INSERT INTO a(i, t) SELECT s, 't' FROM generate_series(10001, 10002) s;
CREATE INDEX "a_i_idx" ON "public"."a" USING btree ("i");
CREATE INDEX "a_t_idx" ON "public"."a" USING btree ("t");
ANALYZE a;
CREATE OR REPLACE FUNCTION a_get_t(in_a a) RETURNS TEXT STABLE LANGUAGE sql
AS 'SELECT $1.t';
explain analyze
select * from a
where a_get_t(a) = 't';
QUERY PLAN
Index Scan using a_t_idx on a (cost=0.00..8.29 rows=2 width=5) (actual
time=0.041..0.043 rows=2 loops=1)
Index Cond: ((t)::text = 't'::text)
==
You may see that a_get_t() SQL code was merged into the main query plan, so
the result is found without a seqscan with t='t' filtering, but the index
is used.
That was a very simple example, I use much more complex SQL+STABLE
functions in my code and I am practically sure that this SQL
extraction+injection is applied by PostgreSQL in other cases too (e.g.
sometimes planner initiates a hash join with tables which are referred
inside SQL+STABLE functions in sub-queries). If I replace STABLE with
VOLATILE in that complex cases, the effect disappears: no more SQL
extraction performed.
And more: assume we have a function f(x, y, z) VOLATILE with very complex
SQL inside and we call it like:
select * from f(1, 2, 3);
The query may took a long time (during not only the first call, but during
all others too). Then I just replace VOLATILE to STABLE for f(), and the
same query:
select * from f(1, 2, 3);
suddenly becomes very fast (e.g. 1000 times faster or even more). It's a
very common case: I've performed many times. I thought that it was because
of re-planning of STABLE functions on each call according to real passed
values...
If STABLE functions has frozen plans too (independent to its real passed
arguments values), how could we explain so much difference in performance
replacing VOLATILE to STABLE?
On Fri, Apr 27, 2012 at 2:34 PM, Albe Laurenz wrote:
> Dmitry Koterov wrote:
> > For example, I have 2 functions like these:
> >
> > CREATE OR REPLACE FUNCTION first(a INTEGER, b INTEGER, ...) RETURNS
> ... AS
> > $body$
> > ...any SQL which uses $1, $2 etc. arguments, plus LIMIT $3...
> > $body$
> > LANGUAGE 'sql'
> > STABLE
> >
> >
> > and
> >
> >
> > CREATE OR REPLACE FUNCTION second(a INTEGER, b INTEGER, ...) RETURNS
> ... AS
> > $body$
> > DECLARE
> > res ...;
> > BEGIN
> > EXECUTE '...the same SELECT, ' ||
> > 'but ' || quote_literal(a) || ' args are embedded, plus ' ||
> > 'LIMIT ' || quote_literal($3)
> > INTO res;
> > RETURN res;
> > END;
> > $body$
> > LANGUAGE 'plpgsql'
> > STABLE
> >
> > And then I call
> >
> > EXPLAIN ANALYZE SELECT * FROM first(...);
> > EXPLAIN ANALYZE SELECT * FROM second(...);
> >
> > Should these two queries be executed by the same time usage (i.e. does
> PostgreSQL generate same plans
> > for inner queries)?
> >
> > I always thought that the answer is YES: if a function is STABLE and
> with language=SQL, its SQL code
> > is embedded into outer context after all arguments are expanded into
> their values (so the plan is
> > built after argument expansion). But some days ago I detected a case
> when second() works about 100
> > times faster than first(), and the cause is seems that the planner
> does not see all of expanded
> > arguments in first() (if I replace arguments to constants in first(),
> especially in LIMIT clause, it
> > begins to work the same speed as second() does). Unfortunately EXPLAIN
> ANALYZE does not go into
> > functions and shows only overall time, so I have no real information
> about what plan is actually used
> > in first().
>
> You can get EXPLAIN plans if you use the auto_explain contrib module
> with auto_explain.log_nested_statements enabled.
>
> As you suspect, the two functions work differently.
> The SQL function will plan a parameterized statement (with $1 etc. in
> it)
> and execute that statement whenever it is called, while the PL/pgSQL
> function will execute an SQL statement with all the constant literals
> in it that gets planned and executed when you call the function.
>
> The SQL function will create a statement that cannot benefit from
> optimizations that work only for certain constant values (although
> there will be improvements in 9.2 for that). On the down side,
> the PL/pgSQL funct
Re: [GENERAL] psql latex and newlines
On Tue, Apr 10, 2012 at 03:18:27PM +0200, Wim Bertels wrote: > On vr, 2012-03-30 at 16:25 +0200, Wim Bertels wrote: > > On do, 2012-03-15 at 16:34 -0300, Alvaro Herrera wrote: > > > Excerpts from Wim Bertels's message of jue feb 23 12:46:29 -0300 2012: > > > > > > > > the problem arises when u have a field value that contains a > > > > > > > > newline > > > > > > > > character, when this field is not the first column, then all > > > > > > > > the data > > > > > > > > after this newline comes in the first column.. > > > > > > > > > > > > > > > > u can try this out, writing a function or table, > > > > > > > > and then add 'enters' or newline in the COMMENT on this > > > > > > > > function or > > > > > > > > table. > > > > > > > the option i came across is the usage of \pbox, > > > > so the suggestion would be to put every field/cell inside a pbox. > > > > > > Did you come up with a patch for this? > > A more polished version on page 7 and 8 of the pdf, > in attachment: > 1. include the psql latex a separate file > 2. use longtable instead of table > 3. if horizontal lines are needed they can easily be uncommented Looks good. Can I see the psql patch for this improvement? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] PLR Help
On 05/02/2012 10:59 AM, Joe Conway wrote: > First, go find lib.R.so. Assuming it is in the above location, place ^-- oops, I meant libR.so Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -- 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] PLR Help
On 05/02/2012 03:01 AM, Daniel Cole wrote: > I finally got R to make after some help on this list, but now I am Does this imply you built R from source, or did you actually install R and R dev from deb packages? If you built R from source you need to use the correct configure switch to ensure the dynamic library gets built, because the default is to not build it. > *sudo psql -d testdb9 -U testdbuser < plr.sql* > > and I get result at the bottom of this email. > I believe that it has to do either with not having $R_Home set correctly > (I can't find must documentation on this) or it has do to with not > having the libR findable by my runtime linker. ( I tried adding the > following to a file that is loaded by /etc/ld.so.conf ) The lines I > loaded to that file are: > /usr/lib/R/bin > /usr/lib/R/ > /usr/lib/postgresql/9.1/lib/ > ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": > libR.so: cannot open shared object file: No such file or directory This error is typically because libR.so is not being found. I am not in a position to check at the moment, but I believe current packaged versions of R place the shared object here: /usr/lib/R/lib First, go find lib.R.so. Assuming it is in the above location, place that location in your /etc/ld.so.conf and run ldconfig. HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -- 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] Limit the normal user to see system catalog or not??? And create privilege???
On Wed, Mar 28, 2012 at 01:54:58PM -0700, Adrian Klaver wrote: > On 03/28/2012 09:54 AM, leaf_yxj wrote: > >For oracle, the normal user can't see all the system catalog. but for > >postgresql, it looks like all the user can see the system catalog. Should > >we limit the user read privilege to system catalog? > > > >In oracle, the system privilege has create table, create view,create > >function. For postgresql database, how to control the user who only can > >create table but can't create view. Based on the test I did, once the user > >has the create privilege on the schema, the user will have any create > >privilege on that schema. In postgresql, Rule is used to control that ??? > >very confused! > > Path to unconfusion:): > http://www.postgresql.org/docs/9.0/interactive/sql-grant.html > > You can grant CREATE on a schema and then restrict CREATE within the > schema for different objects types. In recent versions you are > looking for ALL * IN SCHEMA schema_name where * is the object type. I think the problem with ALL * IN SCHEMA it just applies permissions on all objects in the schema at a point in time, i.e. it doesn't apply to objects created _after_ that command was run. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: How to insert random character data into tables for testing purpose. THanks.
Hi Andreas, Thanks a lot. I tried those following , they work. 1) insert into test values ( generate_series(1,1000), md5(random()::text)); 2) create table t_random as select s, md5(random()::text) from generate_Series(1,5) s; Thanks. Regards. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-insert-random-character-data-into-tables-for-testing-purpose-THanks-tp5680973p5681242.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] new rows based on existing rows
On Wed, May 2, 2012 at 10:52 PM, Andy Chambers wrote: > So ideally, I'd like to be able to do > > insert into foo (a,b,foo_date) > select a,b,now() from foo old where > returning oid, old.oid > > ...but this doesn't work. It seems you only have access to the table > being modified in a returning clause. Is there a way I can return a > simple mapping between old oids and new oids as part of the statement > that inserts the new ones? I'd recommend not using OIDs but having your own ID field (eg a [BIG]SERIAL PRIMARY KEY). Is the mapping of old ID to new ID something that would be worth saving into the table? Even if you don't need it later, that might be the easiest way to do the job. Alternatively, you could play around with joins (an INSERT RETURNING can quite happily be used in a WITH clause) to see if you can get what you want that way. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Which Java persistence library would you use with PostgreSQL?
Hello, I suggest You to use Hibernate. Hibernate has many ways to optimize or fix some situations for particular database, if you allow to edits generated annotations for specific classes. In any case I suggest you to check usage of BLOB. PostgreSQL has two ways to handle this byte[] and large object. Hibernate actually supports both, but sometimes this may be little tricky, especially with auto generation. In such case hibernate specific annotations may help. Regards, Radosław Smogura Dnia Åroda, 2 maja 2012 12:15:35 Bata Degen pisze: > Hi list, > > what is your choice of trust when it comes to Java persistence libraries? > > NetBeans gives me these out of the box: > EclipseLink (JPA 2.0) > Hibernate (JPA 1.0) > TopLink Essentials (JPA 1.0) > > Is any of them known to work very well together with PostgreSQL? Or what > else can you recommend? > > I am looking for a solution to do this (the direction is important, it's > not the other way around): > XML-Schema --[via Hyperjaxb3]--> annotated Java classes --[via JPA]--> > PostgreSQL > > I've already managed to do the first two steps but would appreciate your > advice on the last step. I've done loads of tutorials. This one > http://bit.ly/b7vCkF for example employs Hibernate and the cool thing is > that you can let the relations be created for you automatically. But it > uses Apache Derby as the database backend. This is not what I want. I > want to use PostgreSQL. But when exchanging Derby for PostgreSQL > relations won't be created automatically anymore. > > Has anyone done such magic things successfully with PostgreSQL and if > so, which persistence library where you using? > > Thank you guys for your feedback! > > Regards, > Bata -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to insert random character data into tables for testing purpose. THanks.
leaf_yxj wrote: > Hi Guys, I want to insert the random character data into tables for testing > purpose. > I created a table as follows : > > create table test ( id int, b char(100)); > > I need to insert 10 rows into this table. I know how to insert > generate_series into coloumn ID. But I don't how to insert the Random > string data into column b. something like: test=# create table t_random as select s, md5(random()::text) from generate_Series(1,5) s; SELECT 5 Time: 163,624 ms test=*# select * from t_random ; s | md5 ---+-- 1 | ef82dcb3e8465fa9a7494c569dfa093f 2 | ad6ae62bb4a49c7889aba0bfcba224ff 3 | 27717d30d8cf069b3129853d90ae5035 4 | 530c200678711a414e000c60e73286bd 5 | 2b2c3692da1b60760ff95d41cdf9b82d (5 rows) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Removal of BSDi, BSD/OS port
I assume there are no current users of the BSDi, BSD/OS port that are going to be upgrading to Postgres 9.2. Therefore, I propose removal of that port in Postgres 9.2. Any objections? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Uppercase to lowercase trigger?
On Wed, May 2, 2012 at 10:33 PM, Chrishelring wrote: > Hi, > > I´ve got some tables with column names in lowercase. Before updatering these > tables I want to add a trigger that can convert these lowercase to > uppercase, and when the tables are updates convert them back to lowercase.. Not entirely sure what you're looking for here, but in Postgres, if you don't quote your column names, they are lowercased by default. UPDATE some_table SET ColumnName = 'foo', COLUMNNAME2 = 'bar'; This will work if the table has "columnname" and "columnname2". ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to insert random character data into tables for testing purpose. THanks.
Hi Guys, I want to insert the random character data into tables for testing purpose. I created a table as follows : create table test ( id int, b char(100)); I need to insert 10 rows into this table. I know how to insert generate_series into coloumn ID. But I don't how to insert the Random string data into column b. Please help. Thanks. Grace -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-insert-random-character-data-into-tables-for-testing-purpose-THanks-tp5680973.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to insert random character data into tables.
-- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-insert-random-character-data-into-tables-tp5680962.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PLR Help
On 05/02/2012 05:01 AM, Daniel Cole wrote: Ubuntu 10.04, Lucid Everything below installed by apt-get: Postgres 9.1 Postgres 9.1 dev PostGis R-base R-base-Dev I have wanted to install the PLR via the postgresql-9.1.-plr package, but there doesn't seem to be one for 10.04. 9.1 is the standard version of PostgreSQL for 12.04 LTS. There's a good possibility that you can just install the plr deb from that version: http://packages.ubuntu.com/precise/postgresql-9.1-plr (choose your architecture in the Download section). The plr.sql to load is in /usr/share/postgresql/9.1/extension/plr.sql. Worked for me in 11.04. If you are running the 9.1 packages from the PostgreSQL PPA it will probably work for you, too. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I setup this Exclusion Constraint?
Hi,
I think yes... Just should add WHERE on the end of EXCLUDE...
Sent from my Windows Phone
From: bradford
Sent: 02/05/2012 16:02
To: Misa Simic
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How do I setup this Exclusion Constraint?
It works w/o that range datatype, which I had no idea existed in 9.2.
Anyway, another question. I have col2 as a status of 'pending',
'approved', 'rejected', 'canceled'. I want to exclude overlaps for
'pending' and I want to exclude overlaps for 'approved'. All others
can have overlaps. Is this possible?
On Tue, May 1, 2012 at 2:38 PM, Misa Simic wrote:
> Hi
>
> I think for overlaping exclusion constraint you need period extension
> or range datatype in 9.2
>
> Kind Regards,
>
> Misa
>
> Sent from my Windows Phone
> From: bradford
> Sent: 01/05/2012 19:16
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How do I setup this Exclusion Constraint?
> I would like to prevent overlapping dates ranges for col1 + col2 from
> being inserted into my test table.
>
> Existing Data:
> 1, FOO, 2012-04-04, 2012-04-06
>
> Insert Attempts:
> 1, FOO, 2012-04-05, 2012-04-08 <-- BAD, overlaps w/ above!
> 1, BAR, 2012-04-04, 2012-04-06 <-- OK, no conflict!
> 2, FOO, 2012-04-04, 2012-04-06 <-- OK, no conflict!
>
> Here's the table:
>
> CREATE TABLE test (
> id INTEGER NOT NULL DEFAULT nextval('test_id_seq'),
> col1 INTEGER,
> col2 VARCHAR(10),
> from_ts TIMESTAMPTZ,
> to_ts TIMESTAMPTZ,
> CHECK ( from_ts < to_ts )
> );
>
> I'm trying to used what I learned in
> http://www.depesz.com/2010/01/03/waiting-for-8-5-exclusion-constraints/,
> but I cannot figure out how to apply this exclusion constraint to col1
> (integer) + col2 (varchar).
>
> Also, I'm very new to postgresql, so if you could explain it, that'd
> be great too. And must I compile postgresql from source to gain the
> ability to use this type of exclusion constraint?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I setup this Exclusion Constraint?
It works w/o that range datatype, which I had no idea existed in 9.2.
Anyway, another question. I have col2 as a status of 'pending',
'approved', 'rejected', 'canceled'. I want to exclude overlaps for
'pending' and I want to exclude overlaps for 'approved'. All others
can have overlaps. Is this possible?
On Tue, May 1, 2012 at 2:38 PM, Misa Simic wrote:
> Hi
>
> I think for overlaping exclusion constraint you need period extension
> or range datatype in 9.2
>
> Kind Regards,
>
> Misa
>
> Sent from my Windows Phone
> From: bradford
> Sent: 01/05/2012 19:16
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How do I setup this Exclusion Constraint?
> I would like to prevent overlapping dates ranges for col1 + col2 from
> being inserted into my test table.
>
> Existing Data:
> 1, FOO, 2012-04-04, 2012-04-06
>
> Insert Attempts:
> 1, FOO, 2012-04-05, 2012-04-08 <-- BAD, overlaps w/ above!
> 1, BAR, 2012-04-04, 2012-04-06 <-- OK, no conflict!
> 2, FOO, 2012-04-04, 2012-04-06 <-- OK, no conflict!
>
> Here's the table:
>
> CREATE TABLE test (
> id INTEGER NOT NULL DEFAULT nextval('test_id_seq'),
> col1 INTEGER,
> col2 VARCHAR(10),
> from_ts TIMESTAMPTZ,
> to_ts TIMESTAMPTZ,
> CHECK ( from_ts < to_ts )
> );
>
> I'm trying to used what I learned in
> http://www.depesz.com/2010/01/03/waiting-for-8-5-exclusion-constraints/,
> but I cannot figure out how to apply this exclusion constraint to col1
> (integer) + col2 (varchar).
>
> Also, I'm very new to postgresql, so if you could explain it, that'd
> be great too. And must I compile postgresql from source to gain the
> ability to use this type of exclusion constraint?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL functions not being inlined
Evan Martin writes: > This worked... at first. I did some simple queries and they showed the > function being inlined (index scan on primary key, seq scan - no > function scan). Very happy with that, I tried changing some other > functions (that depend on these) and then found that the _asof functions > are not being inlined anymore! I swear, I'm not making this up. Nothing > changed in those functions. Same simple query. It was inlined before and > now it's not. I've dropped and re-created the functions, did an ANALYZE, > even restarted PostgreSQL - they're not inlined any more. I really don't > know what to think! [ squint... ] There are a lot of undocumented restrictions on inlining in inline_set_returning_function(), but AFAICS none of them are nondeterministic, nor would the decision depend on anything outside the function and its arguments. Can you provide a concrete test case? regards, tom lane -- 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] Significance of Postgres (version 9.1.1) Compilation parameters - Performance
Jayashankar K B wrote: > Could anyone explain me the significance of the following compile time parameters ? > > 1. --disable-integer-datetimes > > 2. --disable-float4-byval > > 3. --disable-float8-byval > > > > Do they have any effect on performance of postgres w.r.t speed of database insert/select, amount of > CPU used during insert/select ? > > How can I know the default compile time parameter values? > > > > The environment in which we are trying to run postgres is a freescale MCF54xx controller based board > having 512MB RAM, 2GB memory with custom built Linux (32-bit environment). The options are well described in http://www.postgresql.org/docs/current/static/install-procedure.html --disable-integer-datetimes changes the internal storage and the precision of date/time data types. Leave the default. The other two options are only needed if you have old server extensions written in C. They affect performance negatively. Leave the default. The default is to have all three options on --enable-* Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] new rows based on existing rows
Hi, I frequently have a need to insert new rows into a table that are based on existing rows but with small changes. This is easy using something like insert into foo (a,b,foo_date) select a,b,now() from foo old where returning oid However in the application layer, I need to know which new record corresponds with which original record So ideally, I'd like to be able to do insert into foo (a,b,foo_date) select a,b,now() from foo old where returning oid, old.oid ...but this doesn't work. It seems you only have access to the table being modified in a returning clause. Is there a way I can return a simple mapping between old oids and new oids as part of the statement that inserts the new ones? Cheers, Andy -- Andy Chambers Software Engineer (e) achamb...@mcna.net (t) 954-682-0573 CONFIDENTIALITY NOTICE: This electronic mail may contain information that is privileged, confidential, and/or otherwise protected from disclosure to anyone other than its intended recipient(s). Any dissemination or use of this electronic mail or its contents by persons other than the intended recipient(s) is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail so that we may correct our internal records. Please then delete the original message. Thank you. -- 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] SQL functions not being inlined
OK, it's now changed back again! I suspended the virtual machine in
which PostgreSQL runs, later resumed it, did some unrelated queries
(SELECT only) and then ran the exact same query as before. It now
returns in 15 ms and uses the index. Here's the query plan:
Index Scan using pk_thing_timeslice on thing_timeslice (cost=0.00..8.32
rows=1 width=382) (actual time=0.058..0.060 rows=1 loops=1)
Index Cond: ((timeslice_id)::integer = 12345)
Filter: (((valid_time_begin)::timestamp without time zone <=
'2012-04-01 00:00:00'::timestamp without time zone) AND ((valid_time_end
IS NULL) OR ('2012-04-01 00:00:00'::timestamp without time zone <
(valid_time_end)::timestamp without time zone)))
Any idea what's going on? It seems like the query optimizer randomly
changes its mind about inlining it.
Regards,
Evan
On 2/05/2012 3:34 PM, Evan Martin wrote:
Thanks, Tom (and Chris). Yes, the EXPLAIN output showed a function scan:
SELECT * FROM thing_asof('2012-04-01') WHERE timeslice_id = 1234
Function Scan on thing_asof (cost=0.25..12.75 rows=5 width=353)
Filter: ((timeslice_id)::integer = 12345)
I replaced the OVERLAPS with < and <= comparisons (since I want the
end time to be exclusive), so the functions now look like this:
CREATE OR REPLACE FUNCTION thing_asof(effective_time timestamp)
RETURNS SETOF thing_timeslice
AS $BODY$
SELECT *
FROM thing_timeslice
WHERE valid_time_begin <= $1 AND (valid_time_end IS NULL OR $1 <
valid_time_end)
$BODY$ LANGUAGE SQL STABLE;
This worked... at first. I did some simple queries and they showed the
function being inlined (index scan on primary key, seq scan - no
function scan). Very happy with that, I tried changing some other
functions (that depend on these) and then found that the _asof
functions are not being inlined anymore! I swear, I'm not making this
up. Nothing changed in those functions. Same simple query. It was
inlined before and now it's not. I've dropped and re-created the
functions, did an ANALYZE, even restarted PostgreSQL - they're not
inlined any more. I really don't know what to think!
Regards,
Evan
On 2/05/2012 1:44 PM, Tom Lane wrote:
Evan Martin writes:
Some of my functions are running much slower than doing the same query
"inline" and I'd like to know if there's a way to fix that. ...
This is quite slow, especially when I have a WHERE clause that narrows
down the set of rows from 100,000 to 10 or so. I suspect it's
evaluating
the function first and filtering it afterwards and perhaps not using
the
index, either.
Well, rather than suspecting, you should use EXPLAIN to find out whether
the functions are inlined or not. The particular example you give here
seems inlinable to me, but maybe some of your other cases aren't.
I concur with the other respondent that OVERLAPS is not the most
efficient way to deal with the sort of condition you have here, either.
Postgres basically doesn't know how to optimize OVERLAPS at all, whereas
the planner has heard of BETWEEN and other simple-comparison constructs.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Uppercase to lowercase trigger?
Hi, I´ve got some tables with column names in lowercase. Before updatering these tables I want to add a trigger that can convert these lowercase to uppercase, and when the tables are updates convert them back to lowercase.. I´ve looked at several examples without any luck. Could someone put me in the direct direction? Thanks Christian -- View this message in context: http://postgresql.1045698.n5.nabble.com/Uppercase-to-lowercase-trigger-tp5680384.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PLR Help
Ubuntu 10.04, Lucid Everything below installed by apt-get: Postgres 9.1 Postgres 9.1 dev PostGis R-base R-base-Dev I have wanted to install the PLR via the postgresql-9.1.-plr package, but there doesn't seem to be one for 10.04. I finally got R to make after some help on this list, but now I am trying to put the plr.sql on a database and its failing. I have read through the instruction page, but know I am not do everything just right in the tips. So what I am trying to run is this: *sudo psql -d testdb9 -U testdbuser < plr.sql* and I get result at the bottom of this email. I believe that it has to do either with not having $R_Home set correctly (I can't find must documentation on this) or it has do to with not having the libR findable by my runtime linker. ( I tried adding the following to a file that is loaded by /etc/ld.so.conf ) The lines I loaded to that file are: /usr/lib/R/bin /usr/lib/R/ /usr/lib/postgresql/9.1/lib/ that last one was just in case that worked. Please can anyone give me an idea how to get PLR running. It is hopefully my last stumbling block before my script that requires R and PLR will run. Thanks, Daniel retunr data from: *sudo psql -d testdb9 -U mkadmin < plr.sql* ** SET ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: function plr_call_handler() does not exist ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: function install_rcmd(text) does not exist ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: type "plr_environ_type" already exists ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: function plr_environ() does not exist ERROR: type "r_typename" already exists ERROR: language "plr" does not exist ERROR: language "plr" does not exist ERROR: type "r_version_type" already exists ERROR: language "plr" does not exist ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: function plr_set_rhome(text) does not exist ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: function plr_unset_rhome() does not exist ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ERROR: function plr_set_display(text) does not exist ERROR: could not load library "/usr/lib/postgresql/9.1/lib/plr.so": libR.so: cannot open shared object file: No such file or directory ** --- Daniel Cole
RE : [GENERAL] Postgres 8.x on Linux : how to use HugePages
Thanks Zoltan, I try it on Kernel Linux 2.6.18 ( RedHat 5.2) 64 bits, it works. I add that ulimit "memlock" (-l) had to be increased for the Postgres runner, up to the size of the shared memory required by Postgres. Didier Sterbecq. Mail : didier.sterb...@rapt.fr -Message d'origine- De : Boszormenyi Zoltan [mailto:z...@cybertec.at] Envoyé : mercredi 2 mai 2012 11:57 À : STERBECQ Didier Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] Postgres 8.x on Linux : how to use HugePages Hi, 2012-05-02 11:28 keltezéssel, STERBECQ Didier írta: We think of using Linux HugePages to isolate shared memory used by Postgres, hoping that we got more performance. I see some souce code samples for doing that, but I do not see any information about compatibility : Postgres 7.x, 8.x, 9.x about recommandations Thanks by advance. Didier Sterbecq. Mail : didier.sterb...@ratp.fr if you are using a kernel older than 2.6.38, you can use this with LD_PRELOAD: http://oss.linbit.com/hugetlb/ With 2.6.38 and up, HugePages are used automatically in giant allocations. Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/
Re: [GENERAL] Which Java persistence library would you use with PostgreSQL?
> value="jdbc:postgresql://localhost:5432/MySampleDb"/> > > If you add ";create=true" just as in the example above, it breaks the code > and you get this as a result: > > org.postgresql.util.PSQLException: FATAL: database "MySampleDb;create=true" > does not exist "create=true" is a Derby specific instruction to create the database if it is missing., it means nothing to the Postgres driver which rightly assumes it is the name of the database you are trying to connect to. To create tables using Hibernate you need to set up your Hibernate config to create the schema for you. The Hibernate docs will give you more information - for 3.x have a look at http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html#configuration-optional. If that doesn't help, maybe the Hibernate user group is better placed to give you more information? - Sumit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Which Java persistence library would you use with PostgreSQL?
To be more specific: When using Hibernate+Derby then in your persistence.xml there's a line like this: value="jdbc:derby://localhost:1527/MySampleDb;create=true"/> If you are using PostgreSQL the line would look like this: value="jdbc:postgresql://localhost:5432/MySampleDb"/> If you add ";create=true" just as in the example above, it breaks the code and you get this as a result: org.postgresql.util.PSQLException: FATAL: database "MySampleDb;create=true" does not exist at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:471) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66) at org.postgresql.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Connection.java:125) at org.postgresql.jdbc3.AbstractJdbc3Connection.(AbstractJdbc3Connection.java:30) at org.postgresql.jdbc3g.AbstractJdbc3gConnection.(AbstractJdbc3gConnection.java:22) at org.postgresql.jdbc4.AbstractJdbc4Connection.(AbstractJdbc4Connection.java:32) at org.postgresql.jdbc4.Jdbc4Connection.(Jdbc4Connection.java:24) at org.postgresql.Driver.makeConnection(Driver.java:393) at org.postgresql.Driver.connect(Driver.java:267) [...] Isn't this automatical table creation possible with PostgreSQL at all? Thanks, again! Bata On 05/02/2012 12:15 PM, Bata Degen wrote: Hi list, what is your choice of trust when it comes to Java persistence libraries? NetBeans gives me these out of the box: EclipseLink (JPA 2.0) Hibernate (JPA 1.0) TopLink Essentials (JPA 1.0) Is any of them known to work very well together with PostgreSQL? Or what else can you recommend? I am looking for a solution to do this (the direction is important, it's not the other way around): XML-Schema --[via Hyperjaxb3]--> annotated Java classes --[via JPA]--> PostgreSQL I've already managed to do the first two steps but would appreciate your advice on the last step. I've done loads of tutorials. This one http://bit.ly/b7vCkF for example employs Hibernate and the cool thing is that you can let the relations be created for you automatically. But it uses Apache Derby as the database backend. This is not what I want. I want to use PostgreSQL. But when exchanging Derby for PostgreSQL relations won't be created automatically anymore. Has anyone done such magic things successfully with PostgreSQL and if so, which persistence library where you using? Thank you guys for your feedback! Regards, Bata -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] updating 6million rows, slowdown every 5minutes, manually doing checkpoints?
hi, i have a table where i need to update 7million rows. i'm trying to do this without a downtime, but doesn't matter what i do, i get massive slowdowns every 5 minutes. details: the table's schema contains 6integers, 2timestamps, 1 varchar, and 1text. i added a new text-field (currently null), and now i need to update it to the correct value(s). basically i need to do 3 queries: update mytable set textfield='text1' where field1=3 and field2=1 and textfield is null; update mytable set textfield='text2' where field1=4 and field2=1 and textfield is null; update mytable set textfield='' where textfield is null; doing them this way would probably cause the database to slow down too much (well, i haven't tried doing it with the live database, but i don't like doing such 'unlimited' queries, because i cannot track it's progress. so for example if it's already running for 5 minutes, i do not know how to see the 'completion percentage' :-( ) so i'm trying to do it 'step by step', like: update mytable set textfield='text1' where idfield in (select idfield from mytable where field1=3 and field2=1 and textfield is null limit 1000); i tried different limits, waiting between queries, but whatever i do, every 5 minutes the db slows down. i checked the documentation and it seems it's doing the CHECKPOINT every 5 minutes. is there anything i could do to make this work better? the documentation recommends to increase checkpoint_timeout, but the current value is generally fine for us, except now, when i need to do this mass-update. could i just do CHECKPOINT commands "manually"? the idea is that if i do it more often than every 5 minutes, maybe the slowdown will not be so massive? or is there perhaps some other way how to 'mass-update' a table in a more efficient way? thanks, gabor -- 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 8.x on Linux : how to use HugePages
Hi, 2012-05-02 11:28 keltezéssel, STERBECQ Didier írta: We think of using Linux HugePages to isolate shared memory used by Postgres, hoping that we got more performance. I see some souce code samples for doing that, but I do not see any information about compatibility : Postgres 7.x, 8.x, 9.x about recommandations Thanks by advance. Didier Sterbecq. Mail : didier.sterb...@ratp.fr if you are using a kernel older than 2.6.38, you can use this with LD_PRELOAD: http://oss.linbit.com/hugetlb/ With 2.6.38 and up, HugePages are used automatically in giant allocations. Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig& Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/
[GENERAL] Significance of Postgres (version 9.1.1) Compilation parameters - Performance
Hi All, Could anyone explain me the significance of the following compile time parameters ? 1. --disable-integer-datetimes 2. --disable-float4-byval 3. --disable-float8-byval Do they have any effect on performance of postgres w.r.t speed of database insert/select, amount of CPU used during insert/select ? How can I know the default compile time parameter values? The environment in which we are trying to run postgres is a freescale MCF54xx controller based board having 512MB RAM, 2GB memory with custom built Linux (32-bit environment). Thanks and Regards Jayashankar Larsen & Toubro Limited www.larsentoubro.com This Email may contain confidential or privileged information for the intended recipient (s). If you are not the intended recipient, please do not use or disseminate the information, notify the sender and delete it from your system. Earth Day. Every Day.
[GENERAL] Postgres 8.x on Linux : how to use HugePages
We think of using Linux HugePages to isolate shared memory used by Postgres, hoping that we got more performance. I see some souce code samples for doing that, but I do not see any information about compatibility : Postgres 7.x, 8.x, 9.x about recommandations Thanks by advance. Didier Sterbecq. Mail : didier.sterb...@ratp.fr
