[SQL] 7.0.3 BUG
SELECT is returning bogus data.
migrate=# select version();
version
---
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.3
(1 row)
migrate=# select userid from users where userid = '[EMAIL PROTECTED]';
userid
-
[EMAIL PROTECTED]
admin
(2 rows)
migrate=# \d users
Table "users"
Attribute | Type |Modifier
--+--+
userrefkey | integer | not null default
nextval('users_userrefkey_seq'::text)
userid | varchar(128) | not null
password1| char(20) | not null
password2| char(50) |
type | char(10) | not null
partneremail | varchar(128) |
adminlastname| char(40) | not null
adminfirstname | char(40) | not null
adminaddress1| char(80) | not null
adminaddress2| char(80) |
admincity| char(80) | not null
adminstateprov | char(40) |
admincountrycode | char(2) | not null
adminpostalcode | char(10) |
adminphone | char(20) |
adminfax | char(20) |
checkpayableto | char(80) | not null
createdate | timestamp| not null default now()
lastaccessdate | timestamp|
lastmodifieddate | timestamp|
Indices: users_pkey,
users_userid_key
[SQL] Is there a string to inet function that can be used in order by ?
I have a table of strings categorized by string type and unique id e.g. stringTypes table (sType, sDescription) 0 vanilla 1 ip address 2 mail address stringValues table (sId, sType, sValue) 1 0 foobar 2 2 123 Main Street 3 1 123.123.123.123 3 1 123.13.123.123 I want to select ip address type strings and order them by ip address. However I can not find a function that returns an inet given a string. e.g. select sValue from stringValues A, stringTypes B where A.sType = B.sType and B.sType = 1 order by [ function that converts string to INET ] (A.sValue); TIA Richard DeVenezia
Re: [SQL] Statement too long
You probably have PostgreSQL compiled with the default blocksize, which is 8k. OpenACS.org has a nice set of instructions explaining how to increase the size to 16k or 32k. http://openacs.org/doc/openacs/html/simple-pg-install-2.html#ss2.2 Hope that helps. Yves Martin wrote: > Hello, > >I have some problem with too long statement. > In 'psql', the error returned for my insert statement is > ERROR: Tuple is too big: size 10436 > > In fact, I use JDBC driver to insert a long long string into a 'text' > field. The exception is
Re: [SQL] Create tables in one query
On Thu, Nov 23, 2000 at 11:40:52AM -0500, [EMAIL PROTECTED] wrote:
> On Thu, 23 Nov 2000, Tomasz Gralewski wrote:
>
> > I'd like to create a few tables in one SQL query, is that possible.
> > What tools I have to use, Perl, Tcl, or maybe is there block command
> > separator that I can write:
> > CREATE TABLE abd
> > (
> > atype,
> > btype,
> > ctype
> > ) and I whant to add the next table here, how can I separate, by this (";"
> > that;s the end of table abd).
>
> Not sure what you are trying to do here. You can put statements like
...
> in a single file and load the file into psql via \i. That's I build all
> of my databases.
Or, if you are concerned with possible failures: enclose the series of
CREATEs in a transaction.
Albert.
--
--
Albert Reiner <[EMAIL PROTECTED]>
Deutsch * English * Esperanto * Latine
--
Re: [SQL] psql question
> "Clayton" == clayton cottingham <[EMAIL PROTECTED]> writes: Clayton> you could use a version of pgsql on your own machine and Clayton> use the host switch to connect to an extrenal db Clayton> of course your db isp will have to allow your ip to Clayton> connect, not an easy thing if your dial up!! Or, assuming you have ssh access, use ssh to setup a tunnel between the two. Sincerely, Adrian Phillips -- Your mouse has moved. Windows NT must be restarted for the change to take effect. Reboot now? [OK]
Re: [SQL] 7.0.3 BUG
"pgsql-sql" <[EMAIL PROTECTED]> writes: > migrate=# select userid from users where userid = '[EMAIL PROTECTED]'; >userid > - > [EMAIL PROTECTED] > admin > (2 rows) That's a tad, um, startling :-( However, you haven't given us nearly enough information to have a shot at figuring out what's going on. regards, tom lane
[SQL] problems with postmaster
Hi all! I couldn't find a way to make my DB server work again. I did this: in postgres user killall -9 postmaster (I wanted to stop the server) then rm /tmp/.s.PGSQL.5432 I tried to restart the server but I got this error: [postgres@aux-209-217-53-170 postgres]$ postmaster -D /usr/local/pgsql/data/ IpcMemoryCreate: shmget failed (Identifier removed) key=5432010, size=144, permi ssion=700 This type of error is usually caused by an improper shared memory or System V IPC semaphore configuration. For more information, see the FAQ and platform-specific FAQ's in the source directory pgsql/doc or on our web site at http://www.postgresql.org. IpcMemoryIdGet: shmget failed (Identifier removed) key=5432010, size=144, permis sion=0 IpcMemoryAttach: shmat failed (Invalid argument) id=-2 FATAL 1: AttachSLockMemory: could not attach segment What I can do?? Help me please! Thank you for your replys, also I want to know how to stop a pg server that is running in the background. Rocael. Get free email and a permanent address at http://www.netaddress.com/?N=1
Re: [SQL] problems with postmaster
ipcclean pg_ctl stop Though finding out about ipcclean was not easy considering the output given. There is no mention of it in any of the FAQs. mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [SQL] problems with postmaster
On Fri, Nov 24, 2000 at 12:34:29PM -0600, Mike Castle allegedly wrote: > ipcclean > pg_ctl stop > > Though finding out about ipcclean was not easy considering the output > given. There is no mention of it in any of the FAQs. > > mrc You could also use ipcrm manually to clean up the shm segments postgresql left lying around. Moral of the story: DON'T USE A SIGKILL TO STOP POSTGRESQL. Of course, there are times when you need to be somewhat crude. Mathijs -- It's not that perl programmers are idiots, it's that the language rewards idiotic behavior in a way that no other language or tool has ever done. Erik Naggum
Re: [SQL] 7.0.3 BUG
Just a wild guess, but I would imagine a corrupt (old) index on the userid field would cause this kind of behavior. You could test this by dropping the index and then rebuilding it. Troy > > "pgsql-sql" <[EMAIL PROTECTED]> writes: > > migrate=# select userid from users where userid = '[EMAIL PROTECTED]'; > >userid > > - > > [EMAIL PROTECTED] > > admin > > (2 rows) > > That's a tad, um, startling :-( > > However, you haven't given us nearly enough information to have a shot > at figuring out what's going on. > > regards, tom lane >
Re: [SQL] 7.0.3 BUG
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > Just a wild guess, but I would imagine > a corrupt (old) index on the userid field would > cause this kind of behavior. A corrupt index might be a contributing factor, but it doesn't seem like it could be the only one. From what I know of the index routines, an entry should not be returned unless it is confirmed to pass the indexqual condition. Corrupt indexes can lead to missing output (because tuples that should match never get visited) but they shouldn't lead to outputting tuples that don't match. In any case, we don't even know whether this query used an indexscan... regards, tom lane
[SQL] How to set autocommit true/false from a java program
Hai, How to set autocommit true/false from a java program. Please explain me. Regards, Ramesh
Re(2): [SQL] 7.0.3 BUG
[EMAIL PROTECTED] writes:
>"pgsql-sql" <[EMAIL PROTECTED]> writes:
>> migrate=# select userid from users where userid = '[EMAIL PROTECTED]';
>>userid
>> -
>> [EMAIL PROTECTED]
>> admin
>> (2 rows)
>
>That's a tad, um, startling :-(
>
>However, you haven't given us nearly enough information to have a shot
>at figuring out what's going on.
>
> regards, tom lane
The query used indexscan. I tried using trim in userid and I got something
like ...
migrate=# select userid from users where trim(userid) = '[EMAIL PROTECTED]';
userid
-
[EMAIL PROTECTED]
(1 row)
I thought it was a varchar problem ('coz userid is varchar) of 7.0.3 so I
changed to 7.0.2.
Is it really a varchar bug?
Anyway, I dumped all the database. When I was restoring it in 7.0.2 I got
these ...
You are now connected as new user root.
CREATE
You are now connected as new user pgsql.
ERROR: copy: line 3910, Bad timestamp external representation '2000-01-05
00:00:60.00+08'
PQendcopy: resetting connection
ERROR: copy: line 3910, Bad timestamp external representation '2000-01-05
00:00:60.00+08'
PQendcopy: resetting connection
ERROR: copy: line 302, Bad timestamp external representation '2000-02-29
00:00:60.00+08'
PQendcopy: resetting connection
ERROR: copy: line 13, Bad timestamp external representation '1970-01-01
08:04:60.00+08'
PQendcopy: resetting connection
ERROR: copy: line 24, Bad timestamp external representation '1970-01-01
08:04:60.00+08'
PQendcopy: resetting connection
You are now connected as new user root.
ERROR: copy: line 2, Bad timestamp external representation '1999-12-24
00:00:60.00+08'
PQendcopy: resetting connection
You are now connected as new user pgsql.
CREATE
I took a look at the database named 'migrate' (this is where the error
occured),
the tables were all empty. Most of the tables have field like this...
createdate | timestamp | not null default now()
Weird because those timestamps were generated by default now(). Is this an
another bug (timestamp bug)?
Take a look at this ...
migrate=# select version();
version
---
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.3
(1 row)
migrate=# select '2000-01-05 00:00:60.00+08'::timestamp;
ERROR: Bad timestamp external representation '2000-01-05 00:00:60.00+08'
migrate=# select '2000-11-25 14:05:23.00+08'::timestamp;
?column?
---
2000-11-25 14:05:23.00+08
(1 row)
migrate=# create table testing (datetime timestamp);
CREATE
migrate=# insert into testing values('2000-01-05 00:00:60.00+08');
ERROR: Bad timestamp external representation '2000-01-05 00:00:60.00+08'
migrate=# insert into testing values('2000-11-25 14:05:23.00+08');
INSERT 6834235 1
migrate=#
Thanks,
sherwin
[SQL] OpenACS
Is there any PostgreSQL developer working on the OpenACS (PG version of the ArsDigita Community Sytem) project? I have installed it and I am very much interested. There are a lot of small bugs though, mostly related to Oracle->Postgres migration, and some which are Postgres specific (queries that should work but don't). I know C programming, and could start hacking in both OpenACS and Postgres to make things work. Some directions from someone who has already dealed with those projects would be VERY appreciated.
Re: [SQL] OpenACS
Edmar Wiggers wrote: > > Is there any PostgreSQL developer working on the OpenACS (PG version of the > ArsDigita Community Sytem) project? > > I have installed it and I am very much interested. There are a lot of small > bugs though, mostly related to Oracle->Postgres migration, and some which > are Postgres specific (queries that should work but don't). Hi Edmars, I am in the OpenACS team. Have you posted the bugs to our Software Development Manager (SDM) (http://openacs.org/sdm)? When we were doing the porting some queries might have slipped, and they're not found unless someone really uses the module, finds and reports them. OpenACS 4 is what we are actively working on right now. It's a much better piece of software than the 3.x series, but it does not have all the modules that 3.x series does, but we'll get there eventually. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
