On Thu, 28 Feb 2008, Richard Greenwood wrote:
I am using text search across multiple columns. Two of the columns
have values that have zero padding - sort of. The values look like
R0001234 (1 char followed by 7 digits, zero padded). Users are
accustom to searching with and without the zero
In my opinion (without looking at the code), if you have a grouping-function
or ORDER BY or GROUP BY clause, then yes, the whole query has to be executed
to show the first row of the result-set. But if the query doesn't have any
of these clauses, then the DB has the ability to send back the first
Klint Gore wrote:
[see below or the top posting police will arrive on my doorstep :)]
Devi wrote:
Hi,
CREATE RULE dosen't require any lock. It is carried out in the parser
level. But there will be ACCESS SHARE lock over the tables which are
being queried are acquired automatically.
On Fri, 29 Feb 2008, Richard Huxton wrote:
Oleg Bartunov wrote:
On Thu, 28 Feb 2008, Richard Greenwood wrote:
So far my best idea is to create a tsvector column containing both
padded and non-padded versions of the value. i.e. put both R1234 and
R0001234 into the tsvector column. This seems
Oleg Bartunov wrote:
On Thu, 28 Feb 2008, Richard Greenwood wrote:
So far my best idea is to create a tsvector column containing both
padded and non-padded versions of the value. i.e. put both R1234 and
R0001234 into the tsvector column. This seems pretty brute force, and
I am pretty new to
On Fri, Feb 29, 2008 at 02:53:05PM +0530, Gurjeet Singh wrote:
In my opinion (without looking at the code), if you have a grouping-function
or ORDER BY or GROUP BY clause, then yes, the whole query has to be executed
to show the first row of the result-set. But if the query doesn't have any
of
On Fri, Feb 29, 2008 at 3:55 PM, Martijn van Oosterhout [EMAIL PROTECTED]
wrote:
On Fri, Feb 29, 2008 at 02:53:05PM +0530, Gurjeet Singh wrote:
In my opinion (without looking at the code), if you have a
grouping-function
or ORDER BY or GROUP BY clause, then yes, the whole query has to be
On Fri, Feb 29, 2008 at 4:08 AM, Tim Rupp [EMAIL PROTECTED] wrote:
One other question. If the lock needed is exclusive, and more inserts
come in after it is requested, will Postgres schedule the rule to be
created before those new inserts are allowed to happen? Or can the rule
request sit
On Fri, Feb 29, 2008 at 05:28:29PM +0530, Gurjeet Singh wrote:
On Fri, Feb 29, 2008 at 3:55 PM, Martijn van Oosterhout [EMAIL PROTECTED]
wrote:
Except if you have an index on the column you're ordering by. Then the
server can really return the first row quickly.
Quickly for sure... but I
On Fri, Feb 29, 2008 at 7:20 PM, Sam Mason [EMAIL PROTECTED] wrote:
On Fri, Feb 29, 2008 at 05:28:29PM +0530, Gurjeet Singh wrote:
On Fri, Feb 29, 2008 at 3:55 PM, Martijn van Oosterhout
[EMAIL PROTECTED] wrote:
Except if you have an index on the column you're ordering by. Then the
Ok, it turns out that the fact that my pg_restore won't work on
windows is directly related to this (from a couple days ago)
Dan Armbrust [EMAIL PROTECTED] writes:
To follow up on my old thread -
I tested another install of Postgres 8.3.0.1 - and on windows, the
postgresql.conf file has this
When inserting into a table and there are many columns to be inserted
it is hard to synchronize columns to values:
insert into my_table (
a,
b,
c,
...many more columns
)values(
@a,
@b,
@c,
... the corresponding values
)
Is there
Steve Clark wrote:
Hello List,
Don't know whether anyone here can help but... We have some code that
has compiled and ran just
fine from postgresql 7.3.x thru 8.2.6. It uses embedded sql. I just
yesterday upgraded our test system to
8.3.0 and this code will no longer compile. Below is a
Sorry, I don't follow.
If I disable that debugger library in the config file, create a
database, backup the database, and then restore the database, I get
many errors - all similar to the one I posted before.
They are reported at the end of the restore as errors.
Are you saying they are really
I can't get views to participate in the hierarchy...
create table outings1 as select * from outings_root limit 0;
alter table outings1 inherit outings_root;
SELECT *
FROM dblink('host=myhost dbname=tacche port=5433 user=postgres
password=postgres'::text,
'SELECT * from
2008/2/29, Martijn van Oosterhout [EMAIL PROTECTED]:
On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote:
When inserting into a table and there are many columns to be inserted
it is hard to synchronize columns to values:
snip
Is there some reason for the insert syntax to be
Hello List,
Don't know whether anyone here can help but... We have some code that
has compiled and ran just
fine from postgresql 7.3.x thru 8.2.6. It uses embedded sql. I just
yesterday upgraded our test system to
8.3.0 and this code will no longer compile. Below is a standalone code
fragment
Dan Armbrust [EMAIL PROTECTED] writes:
It turns out, that commenting out the shared_preload_libraries in
the postgresql.conf file breaks pg_restore.
It hardly breaks anything. The errors you show just indicate that you
had the plpgsql debugger loaded into your database.
On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote:
When inserting into a table and there are many columns to be inserted
it is hard to synchronize columns to values:
snip
Is there some reason for the insert syntax to be the way it is in
instead of the much easier to get it right
On 2/29/08, Scara Maccai [EMAIL PROTECTED] wrote:
I can't get views to participate in the hierarchy...
The partition exclusion _may_ work if you do something like:
create view as
select * from dblink/plproxy-from-part1 where part1 constraint
union all
select * from
Dan Armbrust [EMAIL PROTECTED] writes:
If I disable that debugger library in the config file, create a
database, backup the database, and then restore the database, I get
many errors - all similar to the one I posted before.
Perhaps you've got those functions loaded into template1?
Are you
On 2/29/08, Scara Maccai [EMAIL PROTECTED] wrote:
I'm sorry, I didn't understand you post...
1) Why does my current implementation is not working? Hierarchy doesn't work
with views in general, not only with dblink
Exactly, because inheritance/constraint exclusion wont work with views.
2)
Dan Armbrust escribió:
Sorry, I don't follow.
If I disable that debugger library in the config file, create a
database, backup the database, and then restore the database, I get
many errors - all similar to the one I posted before.
They are reported at the end of the restore as errors.
I'm sorry, I didn't understand you post...
1) Why does my current implementation is not working? Hierarchy doesn't work
with views in general, not only with dblink
2) Why am I supposed to use unions in the view?
3) I know that I am doing select * from tbl in the remote db; that is something
I
Hello,
The Babase project has published the code for it's PostgreSQL
based baboon data management database online.
Babase may be of interest because it makes extensive use
of triggers for data validation and complex data generation;
we use many of PostgreSQL's features to push the limits
when
Exactly, because inheritance/constraint exclusion wont work with views.
Ok, so there should be something written in the docs about it...
From:
the information about a view in the PostgreSQL
system catalogs is exactly the same as it is for a table. So for the
parser, there is absolutely no
If you don't like the standard sql implementation, you could use plsql
or any language to make an abstraction layer/wrapper for this
functionality. Just pass everything as a key/value pair, in an array or
hashtable structure, to your abstraction layer/wrapper, and it can cycle
through the
2008/2/29, Brent Friedman [EMAIL PROTECTED]:
If you don't like the standard sql implementation, you could use plsql
or any language to make an abstraction layer/wrapper for this
functionality. Just pass everything as a key/value pair, in an array or
hashtable structure, to your abstraction
On 2/29/08, Scara Maccai [EMAIL PROTECTED] wrote:
Exactly, because inheritance/constraint exclusion wont work with views.
Ok, so there should be something written in the docs about it...
From:
the information about a view in the PostgreSQL
system catalogs is exactly the same as it
Hi,
I've solved this problem for me (Perl). I have a module DBAPI and write
a function InsertIntoTable($table_name, $hash_with_values,
$data_base_handler).
I send the parms to the function in the hash (key1 = value1, key2
= value2 ...) and in the function I compose the insert and execute
it.
Hey guys, we're changing the way we version our database from some old
unsupported (and crappy) ruby migrations-like methodology to straight
SQL scripts. We currently run CruiseControl.NET on a windows machine
pointed to a test database server hosted on linux for our builds. At
each build we
Hi All,
I am currently running into an issue with a query and would like to get
some assistance if possible.
The PostgreSQL version I am running is 8.0.11 64-Bit, under SuSE Linux
Enterprise Server 9 SP3
I am converting an encoded field (lot_id) into a date field, the 5
character of every
Scara Maccai [EMAIL PROTECTED] writes:
I got that there should be no difference... plus, I don't get any
errors,
You should have. The system enforces (or tries to) that a view can't be
part of an inheritance hierarchy, but you seem to have managed to find a
sequence of operations that avoids
Chris Bowlby wrote:
test=# select tab.dr_prod_date FROM
test-# (SELECT ('01/01/0'::text || substring(ilch.lot_id::text, 5,
1))::date AS dr_prod_date FROM my_lot_test ilch) AS tab
test-# where tab.dr_prod_date = '2/5/08' limit 1;
ERROR: invalid input syntax for type date: 01/01/0W
Using
Hi,
I'm considering an upgrade to 8.3.0 for some internal databases - would
normally wait until at least .1 release of anything for safety but
there's a lot of nice sounding stuff here!
After trawling the bug logs and doing some testing on some servers here
all looks ok except for this bug
Chris Bowlby [EMAIL PROTECTED] writes:
I am converting an encoded field (lot_id) into a date field, the 5
character of every lot_id is always the year and as such I need to
extract the year using the following function:
substring(ilch.lot_id::text, 5, 1)
Well, I'd say that the failure
Chris Bowlby wrote:
Hi All,
I am currently running into an issue with a query and would like to get
some assistance if possible.
The PostgreSQL version I am running is 8.0.11 64-Bit, under SuSE Linux
Enterprise Server 9 SP3
I am converting an encoded field (lot_id) into a date field, the
Hi Colin,
Thanks for your response, if I remove the where clause from my example,
I also am able to execute the query with out issue, as follows:
test=# select tab.dr_prod_date FROM (SELECT ('01/01/0'::text ||
substring(ilch.lot_id::text, 5, 1))::date AS dr_prod_date FROM
my_lot_test ilch) AS
I didn't do anything specific to load those functions into template1.
I was under the impression that that particular debugger wasn't
supposed to be on by default on windows - and that it was an installer
oversight - and that you may turn it off by default for future builds
of the windows
ho Tom,
Thanks that gave me the brain burp I needed to click into what was
causing the root issue.
On Fri, 2008-02-29 at 13:47 -0500, Tom Lane wrote:
Chris Bowlby [EMAIL PROTECTED] writes:
I am converting an encoded field (lot_id) into a date field, the 5
character of every lot_id is
Chris Bowlby wrote:
Hi Colin,
Thanks for your response, if I remove the where clause from my example,
I also am able to execute the query with out issue, as follows:
test=# select tab.dr_prod_date FROM (SELECT ('01/01/0'::text ||
substring(ilch.lot_id::text, 5, 1))::date AS dr_prod_date
Clodoaldo wrote:
2008/2/29, Brent Friedman [EMAIL PROTECTED]:
If you don't like the standard sql implementation, you could use plsql
or any language to make an abstraction layer/wrapper for this
functionality. Just pass everything as a key/value pair, in an array or
hashtable structure, to
To All,
I am running Postgres version 7.4.19.
One thing I wanted to do and have successfully completed is changing the
ownership of a Postgres database and all of its tables to a new owner.
I have also successfully granted access privileges to the new owner.
What I have been unable to do
Chris Bowlby [EMAIL PROTECTED] writes:
ERROR: invalid input syntax for type date: 200W-01-01
the test data I am using for this example is as follows:
FWIW, I don't see any problem here using that test case. Have you tried
looking directly at the output of the substring function, ie
select
Brian is right
change substring(ilch.lot_id::text, 5, 1) and
change '01/01/0'::text ||
to
'01/01/'::text || substring(ilch.lot_id::text,4,2)
M--
- Original Message -
From: brian [EMAIL PROTECTED]
To: pgsql-general@postgresql.org
Sent: Friday, February 29, 2008 1:11 PM
Subject: Re:
Shane Wright [EMAIL PROTECTED] writes:
- Is the HOT/VACUUMFULL bug above dangerous or is its biggest
side-effect an error until a non-full vacuum is run?
As long as you don't build with --enable-cassert, that bug isn't real
nasty. Especially if you don't do VACUUM FULL routinely ;-)
Martin Gainty wrote:
Chris Bowlby wrote:
Hi All,
I am currently running into an issue with a query and would like to get
some assistance if possible.
The PostgreSQL version I am running is 8.0.11 64-Bit, under SuSE Linux
Enterprise Server 9 SP3
I am converting an encoded field (lot_id)
On Fri, Feb 29, 2008 at 01:12:50PM -0500, Carl McCalla wrote:
What I have been unable to do successfully is to remove two users who
should no longer have access to the database or its tables. As super
user postgres, I have attempted to REVOKE access privileges for those
two users, but to
CREATE TABLE fav5.batchnumber (
batchnumberid SERIAL NOT NULL,
processtype SMALLINT NOT NULL,
termloc CHAR(3) NOT NULL,
batchno INTEGER NOT NULL
) WITHOUT OIDS;
With the above table definition, is batchnumberid by default also defined as
the primary key or do I still need to define
Carl McCalla wrote:
To All,
I am running Postgres version 7.4.19.
One thing I wanted to do and have successfully completed is changing the
ownership of a Postgres database and all of its tables to a new owner.
I have also successfully granted access privileges to the new owner.
On Feb 29, 2008, at 2:28 PM, Mike Gould wrote:
CREATE TABLE fav5.batchnumber (
batchnumberid SERIAL NOT NULL,
processtype SMALLINT NOT NULL,
termloc CHAR(3) NOT NULL,
batchno INTEGER NOT NULL
) WITHOUT OIDS;
With the above table definition, is batchnumberid by default also
defined as
On Fri, Feb 29, 2008 at 03:28:11PM -0500, Mike Gould wrote:
CREATE TABLE fav5.batchnumber (
batchnumberid SERIAL NOT NULL,
processtype SMALLINT NOT NULL,
termloc CHAR(3) NOT NULL,
batchno INTEGER NOT NULL
) WITHOUT OIDS;
With the above table definition, is batchnumberid by
Tom
Many thanks :)
I've tried to find this out for myself but have failed :(
I'm assuming that the default RPMs for RHEL 4 (on ftp.postgresql.org) are not
built with --enable-cassert - and that I can veryify this by using the same
reproduction case you demonstrated in the bug history?
(I
On 29. Feb, 19:28 h., abracadabuda [EMAIL PROTECTED] wrote:
Hi,
i need to export pgsql structure of tables, vies, PK, FK etc. etc.
into simple XML structure.
My 3 hour searching ended with one result: postgresql-autodoc, but i
don't have enough rights on server to install it.
Is there any
Hi,
i need to export pgsql structure of tables, vies, PK, FK etc. etc.
into simple XML structure.
My 3 hour searching ended with one result: postgresql-autodoc, but i
don't have enough rights on server to install it.
Is there any solution for my problem - is there any program (best
under Win)?
abracadabuda wrote on 29.02.2008 19:28:
Hi,
i need to export pgsql structure of tables, vies, PK, FK etc. etc.
into simple XML structure.
My 3 hour searching ended with one result: postgresql-autodoc, but i
don't have enough rights on server to install it.
Is there any solution for my problem
Shane Wright [EMAIL PROTECTED] writes:
I'm assuming that the default RPMs for RHEL 4 (on ftp.postgresql.org) are not
built with --enable-cassert - and that I can veryify this by using the same
reproduction case you demonstrated in the bug history?
I believe they aren't, but you could check
Hello,
I am curious if there is a postgresql function that will
return a list of header names based on an input query text.
ie:
select return_headers(SELECT name, date, shape FROM some_table;) as
headers;
returning:
headers
---
name
date
shape
Thanks for any help.
Pw
Hello,
I was writing a statement retrieve dependency information out of the system
catalog, when I noticed something that I didn't expect.
I wanted to use the following statement to translate the relkind column to a
more descriptive value:
select c.relname
case
when
I wanted to use the following statement to translate the relkind
column to a
more descriptive value:
select c.relname
case
when c.relkind in ('t','r') then 'table'
when c.relkind = 'i' then 'index'
when c.relkind = 'S' then 'sequence'
when
On Sat, 1 Mar 2008, Thomas Kellerer wrote:
I was writing a statement retrieve dependency information out of the
system catalog, when I noticed something that I didn't expect.
I wanted to use the following statement to translate the relkind
column to a more descriptive value:
select
The data types of all the result expressions must be convertible to a
single output type.
The type of the field pg_class.relkind appears to be char which is
described in the notes as:
The type char (note the quotes) is different from char(1) in that it
only uses one byte of storage. It is
pw wrote:
Hello,
I am curious if there is a postgresql function that will
return a list of header names based on an input query text.
ie:
select return_headers(SELECT name, date, shape FROM some_table;) as
headers;
returning:
headers
---
name
date
shape
Thanks for any help.
Pw
Adam Rich wrote on 01.03.2008 01:02:
The data types of all the result expressions must be convertible to a
single output type.
The type of the field pg_class.relkind appears to be char which is
described in the notes as:
The type char (note the quotes) is different from char(1) in that it
Anyone?
Or is there a better list for asking installation questions? I could really
use some help on this one.
Dee [EMAIL PROTECTED] wrote: What are the permissions required to install
postgres as a service on windows 2000/2003? I have followed the instructions,
as I understand them, but
abracadabuda wrote:
Hi,
i need to export pgsql structure of tables, vies, PK, FK etc. etc.
into simple XML structure.
My 3 hour searching ended with one result: postgresql-autodoc, but i
don't have enough rights on server to install it.
Is there any solution for my problem - is there any
On Fri, Feb 29, 2008 at 2:28 PM, Mike Gould [EMAIL PROTECTED] wrote:
CREATE TABLE fav5.batchnumber (
batchnumberid SERIAL NOT NULL,
processtype SMALLINT NOT NULL,
termloc CHAR(3) NOT NULL,
batchno INTEGER NOT NULL
) WITHOUT OIDS;
With the above table definition, is batchnumberid
Stephan Szabo [EMAIL PROTECTED] writes:
It looks like the problem is that relkind is of the somewhat odd
PostgreSQL type char not an actual char(1), so with the else in there it
appears to try to force the unknown literals into that type which only
takes the first character. It will probably
pw [EMAIL PROTECTED] writes:
I am curious if there is a postgresql function that will
return a list of header names based on an input query text.
No, but there is support for such things at the wire protocol level
--- specifically, Parse followed by Describe Statement would get you
the result
hmm - I don't suppose the RHEL builds already include your patch against this
issue? If so I guess it would be useful to know :)
I've run through the steps you outlined to reproduce the fault on a new box
(after having reproduced it successfully on test servers), and it does not show
the
On Sat, 1 Mar 2008 03:27:50 -
Shane Wright [EMAIL PROTECTED] wrote:
The RPMs installed from are these:
postgresql-8.3.0-1PGDG.rhel4
postgresql-libs-8.3.0-1PGDG.rhel4
postgresql-server-8.3.0-1PGDG.rhel4
You need postgresql-debug (I think), Devrim?
Joshua D. Drake
Thanks
Hi,
On Fri, 2008-02-29 at 21:31 +, Shane Wright wrote:
I'm assuming that the default RPMs for RHEL 4 (on ftp.postgresql.org)
are not built with --enable-cassert
Yeah, --enable-cassert is enabled only in beta packages.
Regards,
--
Devrim GÜNDÜZ , RHCE
PostgreSQL Replication, Consulting,
72 matches
Mail list logo