Re: [GENERAL] Unsigned Datatypes
The standard doesn't provide for specifically unsigned types, so I do not believe anyone would want to go through that trouble. Sorry. On 2000-01-19, Martin Neumann mentioned: Are there any plans to implement unsigned datatypes like uint4 and uint8? In many cases I don't need the signed int4 numbers, but I would be happy to have a larger range without using the twice as large int8 type. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [GENERAL] Unsigned Datatypes
[Charset ISO-8859-1 unsupported, filtering to ASCII...] The standard doesn't provide for specifically unsigned types, so I do not believe anyone would want to go through that trouble. Sorry. Actually, oid data type is an unsigned int4. If it does not behave that way, it is a bug. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Unsigned Datatypes
On Thu, Jan 20, 2000 at 05:18:36PM -0500, Bruce Momjian wrote: The standard doesn't provide for specifically unsigned types, so I do not believe anyone would want to go through that trouble. Sorry. Actually, oid data type is an unsigned int4. If it does not behave that way, it is a bug. is this just in pgsql, or in SQL in general? -- [ Jim Mercer [EMAIL PROTECTED] +1 416 506-0654 ] [ Reptilian Research -- Longer Life through Colder Blood ] [ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
Re: [GENERAL] Unsigned Datatypes
On Thu, Jan 20, 2000 at 05:18:36PM -0500, Bruce Momjian wrote: The standard doesn't provide for specifically unsigned types, so I do not believe anyone would want to go through that trouble. Sorry. Actually, oid data type is an unsigned int4. If it does not behave that way, it is a bug. is this just in pgsql, or in SQL in general? Just PostgreSQL. Others don't have oids. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] Bug with indexing int4?
Hello All, I ma sorry, I have sent previous uncomplited e-mail accidentally. I have created the table CREATE TABLE "tt" ( "cc" int4); COPY "tt" FROM stdin; -2112563299 -2111287024 -2110307960 . 2146589610 2146589611 2146589612 \. About 30 000 records totally Then I am doing the following: mdb= select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) mdb= create index i_tt_cc on tt (cc); CREATE mdb= select cc from tt where cc = -2112563299; cc (0 rows) mdb= drop index i_tt_cc; DROP mdb= select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) So, whith index postgresql works incorectly in this situation. But when there are less records in the table (about 12 000), everything works well. I tested this on 3 different computers on Debian Linux 2.1 with postgresql 6.5.2 and 6.5.3. Best regards, Yury ICQ 11831432 mailto:[EMAIL PROTECTED]
[GENERAL] Grant problems
Hello I like to create a user who can do the followings on a table: select, insert, update But he can't delete! I tried this: grant INSERT, SELECT, UPDATE on table_name to testuser; After this the test user can delete from the table_name table without any problems. The testuser is not a superuser. Plz help, Thanks, Bye
Re: [GENERAL] Grant problems
Hello I like to create a user who can do the followings on a table: select, insert, update But he can't delete! I tried this: grant INSERT, SELECT, UPDATE on table_name to testuser; After this the test user can delete from the table_name table without any problems. The testuser is not a superuser. Plz help, Thanks, Bye Do the following first: REVOKE ALL ON "table_name" FROM PUBLIC; this takes all permissions away from the Public, eg. all users, including the testuser hope this helps, with kind regards, Joost Roeleveld
[GENERAL] Back-end dying on timestamp value insert
I'm having a problem, both through JDBC and through psql (so I reckon the problem is in the core database) whereby if I put a value into a timestamp field which has a non-unique index on it, I get a "back-end has died unexpectedly" error. It's consistently happening on the same column in the same table, even after destroying and recreating the databases. There is another timestamp column, also indexed, in the same table which behaves perfectly I'm flummoxed. Help! More data: The table is of the form: create table my_table (id int not null, startdate timestamp, enddate timestamp); create unique index id_idx on my_table (id); create index startdate_idx on my_table (startdate datetime_ops); create index enddate_idx on my_table (enddate datetime_ops); insert into my_table (id, startdate) values (1, 'now'); this works fine then: insert into my_table (id, enddate) values (2, 'now'); this fails with backend-death. If I drop the index on enddate I can insert values fine, but then the backend dies if I subsequently create the index. The debugging output, with debuglevel set to 3 is: StartTransactionCommand query: insert into cat_entries (ent_id, ent_code, ent_enddate, ent_tmg_id, ent_is_group) values (455, 'wibble', 'now', 1, 0); ProcessQuery /usr/lib/pgsql/bin/postmaster: reaping dead processes... /usr/lib/pgsql/bin/postmaster: CleanupProc: pid 11763 exited with status 11 /usr/lib/pgsql/bin/postmaster: CleanupProc: reinitializing shared memory and semaphores shmem_exit(0) [#0] binding ShmemCreate(key=52e389, size=1063936) and psql exits with: mydb= insert into cat_entries (ent_id, ent_code, ent_enddate, ent_tmg_id, ent_is_group) values (455, 'wibble', 'now', 1, 0); pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. What's happening? :-) And is it something known about fixed in a later version? (I'm on Postgresql 6.5.1 as supplied with SuSE Linux 6.3) [PostgreSQL 6.5.1 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66] -- Rachel
RE: [GENERAL] Grant problems
test_table has to be created by different user than testuser. If the table is created by testuser he is an owner of the table and can do everything what he wants. Regards, Andrzej -Original Message- From: Balazs Gyetvai [SMTP:[EMAIL PROTECTED]] Sent: 20 stycznia 2000 11:07 To: [EMAIL PROTECTED] Subject: [GENERAL] Grant problems Hello I like to create a user who can do the followings on a table: select, insert, update But he can't delete! I tried this: grant INSERT, SELECT, UPDATE on table_name to testuser; After this the test user can delete from the table_name table without any problems. The testuser is not a superuser. Plz help, Thanks, Bye
Re[2]: [GENERAL] Bug or my crooked hands?
- Original Message - From: Yury Don [EMAIL PROTECTED] To: pgsql-general [EMAIL PROTECTED] Sent: Wednesday, January 19, 2000 11:32 AM Subject: Re[2]: [GENERAL] Bug or my crooked hands? I have the following situation: CREATE TABLE "Contacts" ( "Contact" Serial, [snipped fields] ); Then I have inserted data from text file and do a query mdb= select Contact from Contacts; Contacts --- -2112563299 [snip] ... JR How did you get negative values in a Serial - field?? JR Serial-fields default to positive numbers I have copied the data from MS Access table and there where negative values. I can't change data in field Contacts because they are using in other tables. Speaking of which, what range have you got on that sequence (serial type = sequence AFAIK)? It couldn't be that postgres is looking at your WHERE and deciding that the value is out of range, so there can't be any records to return. What happens if you insert a new record and then try to look for that? -- Richard Huxton Archonet Ltd.
Re: [GENERAL] apache logs to pgsql
On Thu, 20 Jan 2000, Marc Tardif wrote: In the contrib directory, there is an example on how to redirect apache logs to a postgresql database. It says to create a table and change two lines in the apache configuration file to use psql for copying the logs to the database. My question is: using an sql db is mostly compelling for very active sites but, if this is the case, is it sensible to use psql? That's definitely overkill and possibly too slow on busy sites. What you might want to do is write a "psql light" that simply takes strings on the standard input and sends them to the backend. You'd still have to link that against libpq but you might find it to be more efficient. First, I find that using the psql command could have too much overhead. Seeing the executable is 46696 bytes and dynamically linked, I'm not really sure what is the strain on the system for bringing up psql. Not to mention the fact that every time you start it up psql does a whole bunch of initializing, and every line of input you pass it is first parsed before deciding what to do with it. -- Peter Eisentraut Sernanders vaeg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
[GENERAL] Creating simple type aliases
Is it possible to create an alias for an existing type, so that I can refer to it with a simple name, and possibly change it in the future? Something like: create type-alias issn as char(9) Without that it looks like I have to do create type issn ( input=charin, output=charout, internallength=9, externallength=9, element='char' ) then insert a row to pg_opclass, after attempting to use the new type to find its ID. Surely there's an easier way? -- Peter Haworth [EMAIL PROTECTED] "Never tell people how to do things. Tell them WHAT to do and they will surprise you with their ingenuity." -- Gen. George S. Patton, Jr.
Re[3]: [GENERAL] Bug or my crooked hands?
Hello Richard, Once, Thursday, January 20, 2000, 3:04:43 PM, you wrote: RH - Original Message - RH From: Yury Don [EMAIL PROTECTED] RH To: pgsql-general [EMAIL PROTECTED] RH Sent: Wednesday, January 19, 2000 11:32 AM RH Subject: Re[2]: [GENERAL] Bug or my crooked hands? I have the following situation: CREATE TABLE "Contacts" ( "Contact" Serial, RH [snipped fields] ); Then I have inserted data from text file and do a query mdb= select Contact from Contacts; Contacts --- -2112563299 RH [snip] ... JR How did you get negative values in a Serial - field?? JR Serial-fields default to positive numbers I have copied the data from MS Access table and there where negative values. I can't change data in field Contacts because they are using in other tables. RH Speaking of which, what range have you got on that sequence (serial type = RH sequence AFAIK)? RH It couldn't be that postgres is looking at your WHERE and deciding that the RH value is out of range, so there can't be any records to return. RH What happens if you insert a new record and then try to look for that? RH -- RH Richard Huxton RH Archonet Ltd. RH I am already found that this is seems a bug in postgres with using of indexes on int4. I have created the table CREATE TABLE "tt" ( "cc" int4); COPY "tt" FROM stdin; -2112563299 -2111287024 -2110307960 . 2146589610 2146589611 2146589612 \. About 30 000 records totally Then I am doing the following: mdb= select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) mdb= create index i_tt_cc on tt (cc); CREATE mdb= select cc from tt where cc = -2112563299; cc (0 rows) mdb= drop index i_tt_cc; DROP mdb= select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) So, whith index postgresql works incorectly in this situation. But when there are less records in the table (about 12 000), everything works well. I tested this on 3 different computers on Debian Linux 2.1 with postgresql 6.5.2 and 6.5.3. Best regards, Yury ICQ 11831432 mailto:[EMAIL PROTECTED]
[GENERAL] vacuum analyze [table]
Am I the only one who cannot vacuum a named table? (Does it make sense to just vacuum a single table?) regression= \h vacuum Command: VACUUM Description: Clean and analyze a Postgres database Syntax: VACUUM [ VERBOSE ] [ ANALYZE ] [ table ] VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] regression= vacuum analyze num_result; NOTICE: Vacuum: table not found VACUUM The same is true for me with any table, any database, using source cvs'd yesterday. Cheers, Patrick
Re: [GENERAL] Bug with indexing int4?
Yury Don wrote: Hello All, I ma sorry, I have sent previous uncomplited e-mail accidentally. I have created the table CREATE TABLE "tt" ( "cc" int4); COPY "tt" FROM stdin; -2112563299 -2111287024 -2110307960 . 2146589610 2146589611 2146589612 \. About 30 000 records totally Then I am doing the following: mdb=> select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) mdb=> create index i_tt_cc on tt (cc); CREATE mdb=> select cc from tt where cc = -2112563299; cc (0 rows) mdb=> drop index i_tt_cc; DROP mdb=> select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) So, whith index postgresql works incorectly in this situation. But when there are less records in the table (about 12 000), everything works well. I tested this on 3 different computers on Debian Linux 2.1 with postgresql 6.5.2 and 6.5.3. Best regards, Yury ICQ 11831432 mailto:[EMAIL PROTECTED] I tried it on my Debian and it works... hygea=> select version(); version -- PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 (1 row) uname -a Linux Debian 2.0.37 #1 Thu Sep 2 17:24:38 CEST 1999 i586 unknown == CREATE TABLE "tt" ( "cc" int4); CREATE COPY "tt" FROM stdin; select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) create index i_tt_cc on tt (cc); CREATE select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) drop index i_tt_cc; DROP select cc from tt where cc = -2112563299; cc --- -2112563299 (1 row) Jos
[GENERAL] Variable case database names
List of databases Database | Owner +-- Newnham| prlw1 % psql Newnham psql: connection to database "newnham" failed - FATAL 1: Database "newnham" does not exist in the system catalog. template1= \c 'Newnham' FATAL 1: Database "newnham" does not exist in the system catalog. Previous connection kept How can I connect to a database with a variable case name? Cheers, Patrick
[GENERAL] R-trees - any documentation?
Does anyone know where I might find documentation on creating R-tree indexes in PostgreSQL? The docs included in the standard distribution don't seem to even mention the existence of R-trees. Thanks. -- Dan
Re: [GENERAL] vacuum analyze [table]
Am I the only one who cannot vacuum a named table? (Does it make sense to just vacuum a single table?) regression= \h vacuum Command: VACUUM Description: Clean and analyze a Postgres database Syntax: VACUUM [ VERBOSE ] [ ANALYZE ] [ table ] VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] regression= vacuum analyze num_result; NOTICE: Vacuum: table not found VACUUM The same is true for me with any table, any database, using source cvs'd yesterday. Works here. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] parser
I had this problem a couple years ago. I changed the column name before the upgrade by renaming the table to table_old, creating a new table with the original table name by selecting from table_old. For the column I needed to rename, I selected offset as 'x_offset'. I made sure everything was there, then dropped table_old and exported the database prior to the upgrade. It's a bit of trouble to find all the queries in the software and replace the column name, but for my application it was workable. Or maybe you already know how to do this but don't think you should have to? I hope this helps. Sarah Officer officers Jeff MacDonald wrote: hi, got this question from a user, anyone up for some discussion on it 1) I have been using 6.3 and recently tried to move to 6.5. I found that the word "offset" is now a reserved word and I have used it as a field name in a table. 6.5 reports this as an error. I wonder if the parser should do this since the context is as a field name. == Jeff MacDonald [EMAIL PROTECTED] irc: bignose on EFnet ==
Re: [GENERAL] R-trees - any documentation?
Dan Reish wrote: Does anyone know where I might find documentation on creating R-tree indexes in PostgreSQL? The docs included in the standard distribution don't seem to even mention the existence of R-trees. There is some brief mention as to bugs in R-Trees in some part of the documentation. I did not try any further after seeing that, but I'd be willing to help in getting them operable, as I have an application which would benefit considerably from indexed range matches. Sevo
[GENERAL] psql parsing and variable problems
I have been looking at the psql documentation posted on the web
site. I am trying to set variables, but I can't seem to make that
happen. I also notice that psql doesn't seem to parse full
backslash commands. Are the documents out of date, or is the a bug?
\echo foo
is interpreted as \e and pops me into the editor.
\qecho foo
is interpreted as \q and exits psql.
When I try to use a variable, psql doesn't seem to recognize it.
create table status (
code varchar(5) not null,
stat_desc varchar(30) not null);
CREATE
insert into status(code, stat_desc) values ('A1', 'First Status');
INSERT 19082 1
select * from status;
code|stat_desc
+
A1 |First Status
(1 row)
Following the example in the documentation, I try to set a psql
variable, but psql isn't interpreting it the way I expect it to.
\set foo 'status'
select * from :foo;
ERROR: parser: parse error at or near ":"
\set foo status
select * from :foo;
ERROR: parser: parse error at or near ":"
Any suggestions? Is the posted documentation out of date? How can
I use a psql variable? What is the alternative? What I really want
to do is use the 'psql --set' option so I can pass the path to the
plpgsql library from the command line.
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
:plpgsqlpath LANGUAGE 'C';
Any help is appreciated,
Sarah Officer
[EMAIL PROTECTED]
[GENERAL] inserting timestamp values
Hi,
I have troubles inserting the timestamp values. I use postgresql 6.5.3
on RedHat 6.1 (kernel 2.2.13). I do the following
sample== create table t1 (d timestamp);
CREATE
sample== create index t1_d on t1 using btree (d datetime_ops);
CREATE
sample== insert into t1 (d) values ('1999-01-01 14:00:00+03');
INSERT 4668876 1
sample== insert into t1 (d) values ('1999-01-01 14:00:00+03');
pqReadData() -- backend closed the channel unexpectedly.
This possible means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
Can anybody help me to solve this problem ?
Best regards,
Alex
P.S. Before this experiment I have the database in postgresql 6.5.3
too. One of tables in this database contains the timestamp field. I
used the pg_dump utility to backup this database and received the flat
file where all values of this field are in the format
'-mm-dd HH:MM:SS+04'. When I try to restore the database using
this backup-file:
psql mydata backup-file
I receive the error as above.
Re: [GENERAL] weird sum() results
I can understand that. What's weird is that I can do a sum(amount) on the whole table and the value comes out to only My only problem is that the amount field is the money type and doesn't like the casting now. A SELECT SUM(amount::float8) FROM table where state = 'CA'; returns ERROR: No such function 'float8' with the specified attributes And again i'm stuck :-) Thanks in advance for any help. -- |-|| | Hitesh Patel| Voice: (541) 759-3126 | | Facilities Development Manager | Fax: (541) 759-3214 | | Preferred Systems | Email: [EMAIL PROTECTED] | |-||
Re: [GENERAL] psql parsing and variable problems
I have been looking at the psql documentation posted on the web site. I am trying to set variables, but I can't seem to make that happen. I also notice that psql doesn't seem to parse full backslash commands. Are the documents out of date, or is the a bug? \echo foo is interpreted as \e and pops me into the editor. \qecho foo is interpreted as \q and exits psql. The web page manual reflects the coming 7.0 release. We have talked about splitting the docs so there is a stable and development version of the docs, but we haven't do that yet. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
