# [EMAIL PROTECTED] / 2005-07-14 00:57:57 -0400:
On Thu, Jul 14, 2005 at 02:46:01PM +1000, Neil Conway wrote:
Vivek Khera wrote:
The first sentence rules out MySQL, so the second sentence should read
So that leaves Postgres. Your problem is solved ;-)
(If you are accustomed to
I have a simple table to store account names... I want each name to be
unique in a case insensitive manner... but I want the case the user
enters to be remembered so I can't do a simple lower() on the data's way in.
Is there an easy way to go about this? Am I about to write my first
server
Hello,
I am a total newbie to PostgreSql, coming from MS Access background. I have
a question regarding queries in PostgreSql: why do I need to enclose every
field name and table name in quotation marks like
SELECT Name From contacts
That is a major inconvenience when composing a query
This is only really necessary if the table or column names are mixed cases or
include spaces or some such ... normally (?) this is not required. PostgreSQL
relentlessly lower cases such names unless they are double quoted.
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-Original Message-
Neil == Neil Conway [EMAIL PROTECTED] writes:
Neil Does MySQL with InnoDB not qualify as an ACID-compliant database?
Not when you can store a value that is larger than allowed, and it
is silently truncated to be within range. no *I* integrity there.
That's regardless of InnoDB or not.
MySQL
# [EMAIL PROTECTED] / 2005-07-14 02:14:16 -0500:
I have a simple table to store account names... I want each name to be
unique in a case insensitive manner... but I want the case the user
enters to be remembered so I can't do a simple lower() on the data's way in.
CREATE TABLE tbl (col
On Thu, 14 Jul 2005 02:14:16 -0500, Rob Brenart [EMAIL PROTECTED] wrote:
I have a simple table to store account names... I want each name to be
unique in a case insensitive manner... but I want the case the user
enters to be remembered so I can't do a simple lower() on the data's way in.
Am Donnerstag, den 14.07.2005, 00:57 -0400 schrieb Alvaro Herrera:
On Thu, Jul 14, 2005 at 02:46:01PM +1000, Neil Conway wrote:
Vivek Khera wrote:
The first sentence rules out MySQL, so the second sentence should read
So that leaves Postgres. Your problem is solved ;-)
(If you are
Rob Brenart schrob:
I have a simple table to store account names... I want each name to be
unique in a case insensitive manner... but I want the case the user
enters to be remembered so I can't do a simple lower() on the data's
way in.
Is there an easy way to go about this?
Would creating
C locale and en_* locales give different ordering (at least under Linux).
The en_* ordering is case insensitive, and the C locale ordering is case
sensitive because it is simply comparing the ASCII codes.
You could use lower/upper to get case insensitive ordering with C locale.
Okay, that's
On Wed, Jul 13, 2005 at 11:18:04PM -0500, Bob wrote:
Here is the link in case your fingers are broken and it hurts to type;)
http://www.postgresql.org/docs/8.0/interactive/encryption-options.html
I think the Password Storage Encryption paragraph needs a note similar to
what Stephen Frost wrote
David Mitchell wrote:
What is the best way to quickly and reliably stop postgres? We've found
that pg_ctl doesn't work for us very well, frequently failing to
actually stop the postmaster (it times out and reports that it has
failed to stop). This is the same even if we use -m immediate.
Hi all,
I am trying to make a stored procedure in C that is used as a trigger
on before/after insert/update on a certain table. This procedure might do
inserts/updates on the same table (recursively triggering itself). I have
made (pretty) sure that I'm not using 'global' variables in
The first sentence rules out MySQL, so the second sentence should
read So that leaves Postgres. Your problem is solved ;-)
(If you are accustomed to Oracle, you are probably
expecting an ACID
database, which rules out MySQL too).
Does MySQL with InnoDB not qualify as an
Oops, I forgot to say that I have tried PostgreSQL 7.4.1, 7.4.6, and
7.4.7 all with the same result. I'm running this on a Linux (Slackware
10.0), kernel 2.6.10y.
Leif
On Thu, 14 Jul 2005, Leif Jensen wrote:
Hi all,
I am trying to make a stored procedure in C that is used as a
As to make the connection with the data base PostgreSQL 8.0.3 being used the
address localhost in Ipv6([::1 ]) ?
It
is possible?
You only need to use quotes in your SQL statements if your table names
in PostgreSQL contain any upper case letters. PostgreSQL automatically
converts all of your SQL statements to lower case unless they're quoted.
As an alternative to quoting in VB (assuming you're using Access as your
FE),
David Mitchell [EMAIL PROTECTED] writes:
What is the best way to quickly and reliably stop postgres? We've found
that pg_ctl doesn't work for us very well, frequently failing to
actually stop the postmaster (it times out and reports that it has
failed to stop). This is the same even if we
How difficult would it be to add a configuration option (at the global,
database or session level) to make PostgreSQL transparently quote
identifiers for you? That would be a simple way to allow users to use
case-sensitive names without quoting everything manually or changing
deep parts of the
Thomas F. O'Connell [EMAIL PROTECTED] writes:
Anyway, if I do a lookup by oid for 94144936 in pg_class, I don't see
it. And, clearly, it's not in $PGDATA/base/32019395.
You should be looking at relfilenode. See
http://www.postgresql.org/docs/8.0/static/storage.html
and/or use oid2name to
Leif Jensen [EMAIL PROTECTED] writes:
I am trying to make a stored procedure in C that is used as a trigger
on before/after insert/update on a certain table. This procedure might do
inserts/updates on the same table (recursively triggering itself). I have
made (pretty) sure that I'm not
Hi Tom,
Thank you for the suggestions. I didn't know anything about SPI_push and
SPI_pop and I will walk through my code and stuff them in. Am I looking at
some wrong documentation ? I never saw anything about those ?
Do you know any good examples doing such things ?
Leif
On Thu, 14
Leif Jensen [EMAIL PROTECTED] writes:
Thank you for the suggestions. I didn't know anything about SPI_push and
SPI_pop and I will walk through my code and stuff them in. Am I looking at
some wrong documentation ? I never saw anything about those ?
They weren't well documented in old
Solange wrote:
As to make the connection with the data base PostgreSQL 8.0.3 being
used the address localhost in Ipv6([::1 ]) ? It is possible?
I think it should be possible to connect via IPv6, so long as your
operating-system supports it properly. Have you found problems?
PS - did you
Hi again,
Thanks. No changes on this 7.x.x - 8.x.x ?
.. and just to be sure: SPI_exec does the SPI_push/SPI_pop thing too ?
Leif
On Thu, 14 Jul 2005, Tom Lane wrote:
Leif Jensen [EMAIL PROTECTED] writes:
Thank you for the suggestions. I didn't know anything about SPI_push and
The easiest solution is just not to use caps or spaces in your
table/object names, there is no advantage to doing so.
People just need to get over the fact that having caps in a name make it
easier to read.
My Test Table should be my_test_table, the naming makes no difference
to the
On Wed, 2005-07-13 at 23:57, Alvaro Herrera wrote:
On Thu, Jul 14, 2005 at 02:46:01PM +1000, Neil Conway wrote:
Vivek Khera wrote:
The first sentence rules out MySQL, so the second sentence should read
So that leaves Postgres. Your problem is solved ;-)
(If you are accustomed to
Tony Caduto wrote:
The easiest solution is just not to use caps or spaces in your
table/object names, there is no advantage to doing so.
People just need to get over the fact that having caps in a name make it
easier to read.
Not to pick nits, but I disagree. Capitalization (CamelCase in
On Thu, 2005-07-14 at 14:32 +1000, Neil Conway wrote:
Alvaro Herrera wrote:
I don't think you can use just plpgsql's parser. ... it
relies on the main backend parser
If you're content to treat expressions and SQL queries as
opaque strings, you shouldn't need to concern yourself with the
The oid in question does not correspond to a relfilenode, and
oid2name -o 94144936 doesn't return anything when run against the
database in question.
Could this be related to temp tables? We use a lot of them in data
imports, and this was a point of discussion on IRC.
Having a limited
Tom Lane wrote:
Reid Thompson writes:
$ pg_dump -f table_dump.sql -t table_with_error dbname
FATAL 2: read of clog file 1559, offset 0 failed: Success
You've got a clobbered tuple header in that file (ridiculous
xmin or xmax value). Alternatively, given the evident age of
this server, it
Thomas F. O'Connell [EMAIL PROTECTED] writes:
Could this be related to temp tables?
Possibly, given that the table doesn't seem to be there anymore.
Does bgwriter operate on temp tables, and could there exist an edge
condition in which bgwriter might have scheduled a write to disk for
a
[EMAIL PROTECTED] wrote on 07/14/2005 10:38:43 AM:
On Thu, 2005-07-14 at 14:32 +1000, Neil Conway wrote:
Alvaro Herrera wrote:
I don't think you can use just plpgsql's parser. ... it
relies on the main backend parser
If you're content to treat expressions and SQL queries as
Hi guys,
Is there a data type to store data extracted from a
GPS? The only thing I need is store latitude and
longitude.
Thanks in advance,
There are a few options,
Using basic Postgresql:
store the lats as +-90 longs as 0-360 or +-180 numeric datatypes.
create a numeric array
To everyone,
Thanks very much for your replies, and if these responses are any indication
of Postgres quality then I'd be in good hands.
Ted
_
Dont just search. Find. Check out the new MSN Search!
On Jul 13, 2005, at 9:57 PM, Alvaro Herrera wrote:On Thu, Jul 14, 2005 at 02:46:01PM +1000, Neil Conway wrote: Vivek Khera wrote: The first sentence rules out MySQL, so the second sentence should read "So that leaves Postgres". Your problem is solved ;-)(If you are accustomed to Oracle, you are
Hi!
Is there any possibility to get the number of rows resulted by SELECT
CURSOR?
Best Regards, Andrei.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
On Thu, Jul 14, 2005 at 10:49:56AM -0500, Thomas F. O'Connell wrote:
Does bgwriter operate on temp tables, and could there exist an edge
condition in which bgwriter might have scheduled a write to disk for
a file corresponding to a temp table that was removed by sudden
termination of
On Thu, Jul 14, 2005 at 03:38:43PM +, Matt Miller wrote:
The main parser depends (at least) on the List handling and memory
handling.
The PL/PgSQL parser also depends on these, although to a lesser degree.
I suppose these dependencies are okay as long as I can just link my
On Thu, Jul 14, 2005 at 11:07:35AM -0500, [EMAIL PROTECTED] wrote:
Ya know, what would be *totally* cool is to implement Oracle PL/SQL syntax
transparently in PostgreSQL. You just can't do packages in PL/pgSQL. You
can mimic them with schemas, but there are things that must be done
Alvaro Herrera [EMAIL PROTECTED] writes:
I suggested that bgwriter may be the culprit, mainly because the log
lines were not preceded by the log_line_prefix as the other lines in the
log. See an extract here: http://rafb.net/paste/results/awxFnY15.html
Hmm, what are the logging configuration
On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote:
Ron Mayer wrote:
Martijn van Oosterhout wrote:
Well, you get another issue, alignment. If you squeeze your string
down, the next field, if it is an int or string, will get padded to a
multiple of 4 negating most of the
Sorry, I didn't have the evidence about the bgwriter before. It was
based on conjecture on IRC last night and newly gathered evidence
from this morning.
Here's a list of current postgres processes on the box.
postgres 1186 2.8 5.0 437812 417624 ? SJul13 22:37
postgres: writer
Janning Vygen [EMAIL PROTECTED] writes:
and this is the combined statement:
UPDATE temp_gc
SET gc_rank = ranking.rank
FROM (
SELECT
*,
ranking(r1.gc_gesamtpunkte, r1.gc_gesamtsiege) AS rank
FROM (
SELECT
mg_name,
Janning Vygen [EMAIL PROTECTED] writes:
PERFORM n.nspname ,c.relname
FROM
pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
n.nspname like 'pg_temp_%'
AND pg_catalog.pg_table_is_visible(c.oid)
AND
Tony Caduto wrote:
The easiest solution is just not to use caps or spaces in your
table/object names, there is no advantage to doing so.
People just need to get over the fact that having caps in a name make it
easier to read.
My Test Table should be my_test_table, the naming makes no
Thomas F. O'Connell [EMAIL PROTECTED] writes:
Unfortunately, this is a system where the interloper is superuser
(and, yes, changing this has been a TODO). But even so, I need help
understanding how one backend could access the temp table of another.
You'd have to do it pretty explicitly:
On 7/13/05, Nee.Mem(倪明) [EMAIL PROTECTED] wrote:
systemguards,hi!
i see you wrote on this page
http://archives.postgresql.org/pgsql-general/2005-07/msg00319.php
test exsample:
create or replace function test()
returns void as
'
begin
delete
Matt Miller [EMAIL PROTECTED] writes:
... The stuff in src/pl/plpgsql/src/
looked like a good starting point. gram.y and scan.l are there.
pl_funcs.c has some cool-looking dump* functions. Now, can this stuff
be straightforwardly hacked into a program that gets its PL/pgSQL
source code from
Hi
We have a numeric field in our table. When setting
the value of this field to 1.00, we experience the following problem when
using ADO (with the PgOleDB driver) to query the table.
the field comes back correctly identified as a
NUMERIC field, but the actual value of of the field is
Am Donnerstag, den 14.07.2005, 15:38 + schrieb Matt Miller:
On Thu, 2005-07-14 at 14:32 +1000, Neil Conway wrote:
Alvaro Herrera wrote:
I don't think you can use just plpgsql's parser. ... it
relies on the main backend parser
If you're content to treat expressions and SQL
On Jul 14, 2005, at 12:51 PM, Tom Lane wrote:
Thomas F. O'Connell [EMAIL PROTECTED] writes:
Unfortunately, this is a system where the interloper is superuser
(and, yes, changing this has been a TODO). But even so, I need help
understanding how one backend could access the temp table of
So my first instinct was to avoid use of temp tables in this scenario
altogether, but now I'm thinking all I might need to do is unhook the
temp tables from inheritance.
But I just want to raise a basic reliability issu raised in the
nearby Autovacuum loose ends thread issue before I
Thomas F. O'Connell [EMAIL PROTECTED] writes:
does pg_autovacuum as currently written in contrib vacuum temp
tables, and, in 8.0, is this then able (however unlikely) to cause
the sort of error I encountered yesterday?
No, and no, and still no for the integrated version. The
On Thu, Jul 14, 2005 at 04:08:48PM -0500, Thomas F. O'Connell wrote:
So my first instinct was to avoid use of temp tables in this scenario
altogether, but now I'm thinking all I might need to do is unhook the
temp tables from inheritance.
But I just want to raise a basic reliability issu
On Thu, Jul 14, 2005 at 11:30:36AM -0500, Jim C. Nasby wrote:
On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote:
This is a good point. We have always stored data on disk that exactly
matches its layout in memory. We could change that, but no one has
shown it would be a win.
On Wed, Jul 13, 2005 at 04:18:34PM -0400, Vivek Khera wrote:
I concur with this advice. Just use a sequence number which happens
to correspond with your software release numbers... or not. They can
be separate, especially once you get more stable and have more
software updaes than
On Thu, Jul 14, 2005 at 11:18:30AM +1000, Tim Allen wrote:
Others have given you some of the advice I would have given. One more
suggestion - does your database fit in just one schema in the gnumed
database?
It would, for the time being, size-wise. However, we have
conceptually separated the
From this thread, these two bits about PostgreSQL stand out:
I have an old note to myself that persistent write errors could clog
the bgwriter, because I was worried that after an error it would
stupidly try to write the same buffer again instead of trying to make
progress elsewhere. (CVS tip
I'm using PG 8.0.1 on Gentoo Linux (kernel 2.6.10 SMP) on Pentium 4 w/ HT.
I run the following as root:
su postgres -c pg_ctl stop -D /var/lib/postgresql/data -m fast
Or with -m immediate. Some of the backends stop but others never
shutdown. Even if I do a killall postmaster (just with
Klint Gore wrote:
On Thu, 14 Jul 2005 02:14:16 -0500, Rob Brenart [EMAIL PROTECTED] wrote:
I have a simple table to store account names... I want each name to be
unique in a case insensitive manner... but I want the case the user
enters to be remembered so I can't do a simple lower() on
David Mitchell [EMAIL PROTECTED] writes:
I run the following as root:
su postgres -c pg_ctl stop -D /var/lib/postgresql/data -m fast
Or with -m immediate. Some of the backends stop but others never
shutdown. Even if I do a killall postmaster (just with sigterm, not -9),
these backends hang
Hi
apache test page is showing at 2005-07-15 09:00 AEST.
Joe.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On Thu, Jul 14, 2005 at 11:29:23PM +0200, Martijn van Oosterhout wrote:
On Thu, Jul 14, 2005 at 11:30:36AM -0500, Jim C. Nasby wrote:
On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote:
This is a good point. We have always stored data on disk that exactly
matches its layout in
back up ...
On Fri, 15 Jul 2005, Joe Healy wrote:
Hi
apache test page is showing at 2005-07-15 09:00 AEST.
Joe.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Marc G. Fournier Hub.Org Networking Services
One other detail: pg_autovacuum is running on this system.
I just noticed this from Tom's Autovacuum loose ends post from
earlier today:
The code does not make a provision to ignore temporary tables.
Although vacuum.c and analyze.c will disregard the request to touch
such tables, it'd
Am Mittwoch, 13. Juli 2005 16:04 schrieb Tom Lane:
Janning Vygen [EMAIL PROTECTED] writes:
I was just testing some configuration settings, especially increasing
shared_buffers and setting fsync to false. And suddenly it happens 3
times out of ten that i get this error.
Could you put
Am Mittwoch, 13. Juli 2005 15:35 schrieb Tom Lane:
Janning Vygen [EMAIL PROTECTED] writes:
this way it works:
CREATE TEMP TABLE ranking AS *Q*;
EXECUTE 'UPDATE temp_gc SET gc_rank = ranking.rank
FROM ranking WHERE temp_gc.mg_name = ranking.mg_name;';
and this way it doesn't:
Hi,
I am wondering if anyone can tell me how I can obtain only the list of
data table in postgresql without function and other ancillary tables. I
hope that I can add a tag that can filter only data table.
I am using the following SQL Statement:
SELECT TABLE_NAME, TABLE_TYPE FROM
On Jul 13, 2005, at 10:00 AM, Google Mike wrote:
Using PHP and PostgreSQL only, what do you feel are the most popular
CMS and RAD tools out there?
Drupal is a very nice CMS for PHP and PostgreSQL. I'm using it for
some consulting projects now and it has saved me a great deal of
time.
I was afraid of that, it's such a useless duplication of effort, but
what can you do... what's EMS Manager though, I'm just using phpPgAdmin
EMS is a more polished version phpPgAdmin has a lot more features
I dunno if I'd define the same way you have, one of the great things
about
71 matches
Mail list logo