Re: [GENERAL] change the last bit
[EMAIL PROTECTED] wrote: > I have a int4 coloumn, and I want to change the last bit the the > number in this column to 0. How can I do it? Last bit = least significant bit (LSB)? update mytable set thecolumn = thecolumn & ~1 where ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] make install (in contrib) and PGDATA
CSN wrote: > For this to work, make sure that: > . the cube source directory is in the postgres contrib > directory > . the user running "make install" has postgres > administrative authority > . this user's environment defines the PGLIB and PGDATA > variables and has > postgres binaries in the PATH. These installation instructions are completely bogus. The new text that I just committed reads: The user running "make install" may need root access; depending on how you configured the PostgreSQL installation paths. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] (Again) Datacorruption using 7.4.2 on XFS/raid1
On Mon, 12 Jul 2004 20:31:15 +0200, Florian G. Pflug <[EMAIL PROTECTED]> wrote: > Hi > > We have again experienced data-corruption using 7.4.2 on an XFS Filesystem > on top of a software-raid (md) raid-1. > > After a server crash last night (It was a rather strange crash - The machine > was still pingable, but no login was possible, and postgres and apache > didn't respond to requests any more) we hard-reset the machine. It came up > again nicely, but a few hours later the following errors occured when trying > to access certain tabled. (Those tables are updated heavily - each day about > 2 million tuples are inserted, and the old versions of those tuples > deleted). > > ERROR: could not access status of transaction 34048 > DETAIL: could not open file "/var/lib/postgres/data/pg_clog/": No such > file or directory You don't say what kind of disks you are using. Sounds very much like hardware problems though. I had a PostgreSQL installation on a pair of IDE disks with software RAID1 / Ext3 die very nastily with similar error messages. Turned out that one of the disks was very defective and the RAID wasn't handling it. On the other hand - after copying the files from the good disk, PostgreSQL started with barely a complaint and I couldn't detect any corruption. Ian Barwick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Latitude/Longitude data types and functions
CSN <[EMAIL PROTECTED]> writes: > I looked through the docs and contrib, but didn't see > anything related to storing and using latitude and > longitude values. I think you want to look at the PostGis project: http://postgis.refractions.net regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] make install (in contrib) and PGDATA
Ah, that should work. The README for contrib/cube states: To install the type, run make make install For this to work, make sure that: . the cube source directory is in the postgres contrib directory . the user running "make install" has postgres administrative authority . this user's environment defines the PGLIB and PGDATA variables and has postgres binaries in the PATH. So I did: $ export PGLIB=/usr/lib/pgsql $ export PGDATA=/var/lib/pgsql/data $ make $ make install mkdir /usr/share/pgsql/contrib make: *** [installdirs] Error 1 $ pg_config --configure '--prefix=/usr' '--datadir=/usr/share/pgsql' '--libdir=/usr/lib/pgsql' '--includedir=/usr/include/pgsql' Is PGLIB and/or PGDATA ignored? What should I do so 'make install' works? Thanks, CSN --- Bruce Momjian <[EMAIL PROTECTED]> wrote: > > Didn't /contrib/earthdistance help? > > --- > > CSN wrote: > > I looked through the docs and contrib, but didn't > see > > anything related to storing and using latitude and > > longitude values. I have data in the form of 12° > 34' > > N, 12° 34' W. > > > > Would any of the geometric data types work well > for > > this type of data? Or just convert them to > decimals? > > Are there any functions for converting lat/long > > to/from decimals/DDMMSS NSEW? > > > > Thanks > > > > > > > > __ > > Do you Yahoo!? > > Yahoo! Mail is new and improved - Check it out! > > http://promotions.yahoo.com/new_mail > > > > ---(end of > broadcast)--- > > TIP 2: you can get off all lists at once with the > unregister command > > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) > > > > -- > Bruce Momjian| > http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) > 359-1001 > + If your life is a hard drive, | 13 Roberts > Road > + Christ can be your backup.| Newtown > Square, Pennsylvania 19073 > __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] (Again) Datacorruption using 7.4.2 on XFS/raid1
FYI, I have seen the SW linux raid not detect failed drives and cause filesystem corruption on many occasions. I would reccomend staying away from it. Maybe what you describe is a problem with PG but, i doubt it. On Jul 12, 2004, at 12:31 PM, Florian G. Pflug wrote: Hi We have again experienced data-corruption using 7.4.2 on an XFS Filesystem on top of a software-raid (md) raid-1. After a server crash last night (It was a rather strange crash - The machine was still pingable, but no login was possible, and postgres and apache didn't respond to requests any more) we hard-reset the machine. It came up again nicely, but a few hours later the following errors occured when trying to access certain tabled. (Those tables are updated heavily - each day about 2 million tuples are inserted, and the old versions of those tuples deleted). ERROR: could not access status of transaction 34048 DETAIL: could not open file "/var/lib/postgres/data/pg_clog/": No such file or directory While reading linux-kernel today, I stumbled upon a description of a rather strange XFS behaviour. It seems to zero a block if the block was updated, and the corresponding metadata-update was flushed to disk, but not the data itself. It does not happen if the file is fsynced() after the update - but I was wondering what would happen if the machine crashed between the write() and the fsync(). The lkml thread about this can be found here: http://www.ussg.iu.edu/hypermail/linux/kernel/0407.1/0359.html Could this XFS behaviour cause the postgres problems we are seeing? greetings, Florian Pflug ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Can connection pointer be obtained from PGresult?
I inherited some C code that works as a series of libraries using libpq. One of the libraries has an input to it of a PGresult pointer, but not of a PGconnect pointer. Within the library, the code tries to determine the host name of the connection by doing the following trick: strcpy (host,PQhost(((PGresult*)*pgresult)->xconn)); This worked fine in the past (v. 7.0) despite being warned against in the documentation since directly accessing the structures in PGresult seems to be off limits (I suppose in case its structure changes with PG version). I'm trying to think of a workaround that would do things right without having to explicitly pass PGconn pointer into the code. Perhaps this can't be done, but I thought I'd throw out the question and wait for the verbal abuse. -Tony ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Latitude/Longitude data types and functions
Didn't /contrib/earthdistance help? --- CSN wrote: > I looked through the docs and contrib, but didn't see > anything related to storing and using latitude and > longitude values. I have data in the form of 12° 34' > N, 12° 34' W. > > Would any of the geometric data types work well for > this type of data? Or just convert them to decimals? > Are there any functions for converting lat/long > to/from decimals/DDMMSS NSEW? > > Thanks > > > > __ > Do you Yahoo!? > Yahoo! Mail is new and improved - Check it out! > http://promotions.yahoo.com/new_mail > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Latitude/Longitude data types and functions
I looked through the docs and contrib, but didn't see anything related to storing and using latitude and longitude values. I have data in the form of 12° 34' N, 12° 34' W. Would any of the geometric data types work well for this type of data? Or just convert them to decimals? Are there any functions for converting lat/long to/from decimals/DDMMSS NSEW? Thanks __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] (Again) Datacorruption using 7.4.2 on XFS/raid1
Hi We have again experienced data-corruption using 7.4.2 on an XFS Filesystem on top of a software-raid (md) raid-1. After a server crash last night (It was a rather strange crash - The machine was still pingable, but no login was possible, and postgres and apache didn't respond to requests any more) we hard-reset the machine. It came up again nicely, but a few hours later the following errors occured when trying to access certain tabled. (Those tables are updated heavily - each day about 2 million tuples are inserted, and the old versions of those tuples deleted). ERROR: could not access status of transaction 34048 DETAIL: could not open file "/var/lib/postgres/data/pg_clog/": No such file or directory While reading linux-kernel today, I stumbled upon a description of a rather strange XFS behaviour. It seems to zero a block if the block was updated, and the corresponding metadata-update was flushed to disk, but not the data itself. It does not happen if the file is fsynced() after the update - but I was wondering what would happen if the machine crashed between the write() and the fsync(). The lkml thread about this can be found here: http://www.ussg.iu.edu/hypermail/linux/kernel/0407.1/0359.html Could this XFS behaviour cause the postgres problems we are seeing? greetings, Florian Pflug ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] cannot access or delete view
On Mon, Jul 12, 2004 at 03:03:14PM +0200, Holger Marzen wrote:
> But "select * from v_laufwerke" gives "FEHLER: Relation >>v_laufwerke<<
> existiert nicht" (ERROR: Relation v_laufwerke doesn't exist). Dropping
> fails as well although it seems to be there:
>
> login=# \dv
>List of relations
> Schema |Name | Type | Owner
> +-+--+--
> public | v_laufverke | view | postgres
> (1 row)
I see a "v" in the name instead of a "w" ?
--
Alvaro Herrera ()
"Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)
---(end of broadcast)---
TIP 8: explain analyze is your friend
[GENERAL] SOLVED: cannot access or delete view
I had tomatoes on my eyes. I wrote laufverke but meant laufwerke.
Sorry.
-- Forwarded message --
Date: Mon, 12 Jul 2004 15:03:14 +0200 (CEST)
From: Holger Marzen <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: cannot access or delete view
Hi all,
I installed PostgreSQL 7.4.2 from backports.org and all went fine. Then
I did as user postgres:
create view v_laufverke
as
select
distinct
bg.mandant as v_mandant,
bg.benutzer as v_benutzer,
l.laufwerk as v_laufwerk,
l.pfad as v_pfad,
l.benutzer as v_login_benutzer,
l.kennwort as v_login_kennwort
from
laufwerk l,
laufwerk_in_gruppe lg,
benutzer_in_gruppe bg
where
l.mandant = lg.mandant and
l.mandant = bg.mandant and
l.laufwerk = lg.laufwerk and
lg.gruppe = bg.gruppe and
bg.mandant = lower('201') and
bg.benutzer = lower('Marzen');
But "select * from v_laufwerke" gives "FEHLER: Relation >>v_laufwerke<<
existiert nicht" (ERROR: Relation v_laufwerke doesn't exist). Dropping
fails as well although it seems to be there:
login=# \dv
List of relations
Schema |Name | Type | Owner +-+--+--
public | v_laufverke | view | postgres
(1 row)
\dv v_laufwerke returns "No matching relations found." (in english).
Did I miss something?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] cannot access or delete view
Hi all,
I installed PostgreSQL 7.4.2 from backports.org and all went fine. Then
I did as user postgres:
create view v_laufverke
as
select
distinct
bg.mandant as v_mandant,
bg.benutzer as v_benutzer,
l.laufwerk as v_laufwerk,
l.pfad as v_pfad,
l.benutzer as v_login_benutzer,
l.kennwort as v_login_kennwort
from
laufwerk l,
laufwerk_in_gruppe lg,
benutzer_in_gruppe bg
where
l.mandant = lg.mandant and
l.mandant = bg.mandant and
l.laufwerk = lg.laufwerk and
lg.gruppe = bg.gruppe and
bg.mandant = lower('201') and
bg.benutzer = lower('Marzen');
But "select * from v_laufwerke" gives "FEHLER: Relation >>v_laufwerke<<
existiert nicht" (ERROR: Relation v_laufwerke doesn't exist). Dropping
fails as well although it seems to be there:
login=# \dv
List of relations
Schema |Name | Type | Owner
+-+--+--
public | v_laufverke | view | postgres
(1 row)
\dv v_laufwerke returns "No matching relations found." (in english).
Did I miss something?
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] win32 port
> > ...i have little experience in linux and i work on windows. > > > I don't know the current state of the Windows port (PG 7.5) > but as I understood it currently won't run as service at all. > For getting a service you need to go with cygwin ... Current snapshots have full service integration - has had for quite a while. The integratino happens through pg_ctl, so run that command to get the parameters required to install the service. > I didn't test it, but at least since Windows 2000 there is a > command "runas" that can RUN programs AS other users. > You can use it even with the desktop, when you hold SHIFT and > right-click a program. This command works just fine - this is how I start my dev version. You can also specify it directly on the shortcut so it will always pop up. It exists in 2000, XP and 2003. (And Longhorn, but let's run one development snapshot at a time..) //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] win32 port
Hallo Hans, [EMAIL PROTECTED] wrote: ...i have little experience in linux and i work on windows. I don't know the current state of the Windows port (PG 7.5) but as I understood it currently won't run as service at all. For getting a service you need to go with cygwin ... I didn't test it, but at least since Windows 2000 there is a command "runas" that can RUN programs AS other users. You can use it even with the desktop, when you hold SHIFT and right-click a program. ...and - as far as i can see - to handle users is a young and not easy to use feature in most windows-systems. "young" as in it's teens ;) Windows NT => 2000 => XP => ... had user-accounts for years and the access rights are nicely configurable. You can even take away Administrator's rights to access directories or files, though in most cases he can set it back himself. This way you can prevent accidentally erasing stuff. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] help required
Ramesh Yeligar wrote: Hi, We have been using pgsql for our retail business, now, due hard drive crash, the databse corrupted and we are unable to start pgsql database. Pl help me if you know any commands or tools to recover this database. What do you have in hand? like the PGDATA folder pg_xlog ? do you have the database backups? Regds Mallah. Thanks, Ramesh ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- regds Mallah. Rajesh Kumar Mallah +---+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---+ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Difference in text/char data matching between 7.3.4
Hi Tom, Thanks for your input. So if I understand you correctly, it is implied that the behavior with PostgreSQL 7.3.4 is likely to be incorrect (or it was a bug, I guess?) One more question, in your reply, you mentioned that "char" type considers trailing spaces to be insignificant. Should this idea be applied to both how "char" type holds whitespaces and how equal operator works on "char" type? Thanks, Kazuya Tom Lane wrote: Kazuya Togashi <[EMAIL PROTECTED]> writes: With version 7.3.4, following query returns true, but with version 7.4.2 it returns false. select ' '::text = ' '::char; Offhand that seems like a step forward to me. "char" type considers trailing spaces to be insignificant, so it is really correct to consider that ' '::char is equivalent to ''::text, not ' '::text which represents a single significant space. If you do not like this behavior, I suggest casting your text input to char so that the comparison will occur under char rules not text rules. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Help needed on time data types
mike wrote: > All the date-time types seem to only be for specific time dates and > not for a running total. You probably want the interval type. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Help needed on time data types
mike wrote: I cannot seem to find a field type in postgres that is equivalent to h:mm without enforcing a 24 hour day limit. This seems to preclude pg from use - am I correct? You want to use "interval" data type and then do some formatting on the output to get 123 hours 30 minutes. See: http://www.postgresql.org/docs/7.4/interactive/datatype-datetime.html -- Michal Taborsky http://www.taborsky.cz ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postgress latest Windows Version
[EMAIL PROTECTED] (Praveen) wrote in message news:<[EMAIL PROTECTED]>... > Can anybody tell from where i can download the latest Postgress Windows version? > http://www.hagander.net/pgsql/win32snap/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Help needed on time data types
Hi I am trying to setup a timesheet app. However I have come up against an unexpected problem (possibly fatal) I cannot seem to find a field type in postgres that is equivalent to h:mm without enforcing a 24 hour day limit. This seems to preclude pg from use - am I correct? All the date-time types seem to only be for specific time dates and not for a running total. Any help appreciated. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
