Re: [ADMIN] Disabling triggers on a relation.
by total no of triggers that relation has, which is got from a subselct on pg_triggers On Thursday 05 December 2002 10:24 am, shreedhar wrote: Hi Bhuvan Mallah, Have you checked desabling of triggers. What reltriggers represent. If we set it as '0' to enable by what value it has to be updated. Sreedhar - Original Message - From: Bhuvan A [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 02, 2002 3:16 PM Subject: Re: [ADMIN] Disabling triggers on a relation. What is the correct method of temporarily disabling all triggers from a table? Disable: db=# update pg_class set reltriggers = '0' where relname = 'your_table'; Enable: db=# update pg_class set reltriggers = (select count(*) from pg_trigger where pg_class.oid = tgrelid) where relname = 'your_table'; also is there any command/function to show the trigger defination ? like pg_index_def does for indexes? Not sure. regards, bhuvaneswaran ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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 -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] how to cancel a query ?
On Wednesday 04 December 2002 17:47, Tom Lane wrote: Andreas Schmitz [EMAIL PROTECTED] writes: ist there any way to cancel a user query as dba ? Send a SIGINT to the backend process running that query, eg kill -INT pid This has the same effect as a user-requested cancel (eg ^C in psql). What about an implementation into the command structure ? regards -andreas -- Andreas Schmitz - Phone +49 201 8501 318 Cityweb-Technik-Service-Gesellschaft mbH Friedrichstr. 12 - Fax +49 201 8501 104 45128 Essen - email [EMAIL PROTECTED] ---(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: [ADMIN] PAM
I was testing this on a 7.3 beta the other week to try to make it work with LDAP authentication, and I think I only got it working if I bypased the system-auth PAM file that everything was normally funneled through. I don't know exactly why it wasn't working, but whenever I put a line that used pam_unix.so it would fail with the same error you are encountering. I didn't invest a huge amount of time figuring out why pam_unix.so was causing a problem because I wanted to use pam_ldap.so anyways. There doesn't seem to be many examples out there that I could find that I got to work. FYI I am testing on RedHat 7.x and 8.0 machines. Tim EMOTO Masahiko wrote: Does anyone show me a sample of PAM authenticate file? I want to use pam for client authentication, and I create pg_hba.cnf as, hostall all 127.0.0.1 255.255.255.255 trust hostall all 192.168.0.0255.255.0.0 pam postgresql and /etc/pam.d/postgresql as auth required /lib/security/pam_stack.so service=system-auth accountrequired /lib/security/pam_stack.so service=system-auth I tried to connect to the server, but failed. The messages I received were follows; [CLIENT] % psql -h dgpc1 db1 -U emo Password: psql: FATAL: PAM authentication failed for user emo [SERVER] DEBUG: reaping dead processes DEBUG: child process (pid 15642) exited with exit code 0 DEBUG: BackendStartup: forked pid=15643 socket=8 DEBUG: received PAM packet LOG: CheckPAMAuth: pam_authenticate failed: 'Authentication failure' FATAL: PAM authentication failed for user emo DEBUG: proc_exit(0) DEBUG: shmem_exit(0) DEBUG: exit(0) DEBUG: reaping dead processes The user account exists in the database, and I typed the system password. Environment: OS : Linux Kernel 2.4.19 PostgreSQL 7.3 By the way, what really I want to do is to configure the server behave like FTP servers; All the users except the guest (anonymous) requires the password authentication. The guest user can only read the data, and cannot alter the data. Are there any solutions to do this? --- EMOTO Masahiko --- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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: [ADMIN] how to cancel a query ?
If you wanted to do something programatically, couldn't you define some functions that would allow you say implement a query id, and embed a function call in the where clause like select getNextQueryId() where .. and queryStillActive(query_id) = true Then another function could be called to kill the query either from within the program, or externally like stopQuery(query_id). You probably wouldn't want to do this for every query, but if you know you have some potentially long running queries, then Eric -Original Message- From: Andreas Schmitz [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 7:51 AM To: Tom Lane; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [ADMIN] how to cancel a query ? On Wednesday 04 December 2002 17:47, Tom Lane wrote: Andreas Schmitz [EMAIL PROTECTED] writes: ist there any way to cancel a user query as dba ? Send a SIGINT to the backend process running that query, eg kill -INT pid This has the same effect as a user-requested cancel (eg ^C in psql). What about an implementation into the command structure ? regards -andreas -- Andreas Schmitz - Phone +49 201 8501 318 Cityweb-Technik-Service-Gesellschaft mbH Friedrichstr. 12 - Fax +49 201 8501 104 45128 Essen - email [EMAIL PROTECTED] ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] list schema
Bruce, I wonder which command can show all schemas in 7.3? I mean: \dt -- show all tables \l -- show all databases ?? -- show all schemas Thanks. Jie Liang Software Engineer St. Bernard Software 16882 W. Bernardo Dr. San Diego, CA 92127 Tel: 858-524-2134 Fax:858-676-2228 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] how to cancel a query ?
Andreas Schmitz wrote: On Wednesday 04 December 2002 17:47, Tom Lane wrote: Andreas Schmitz [EMAIL PROTECTED] writes: ist there any way to cancel a user query as dba ? Send a SIGINT to the backend process running that query, eg kill -INT pid This has the same effect as a user-requested cancel (eg ^C in psql). What about an implementation into the command structure ? Libpq has: PQrequestCancel() Where else did you want it? -- 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])
[ADMIN] fresh 7.3-1 SRPMS bug
postgresql.init script contains line: if [ `cat $PGDATA/PG_VERSION` != '7.2' ] while it of course should be: if [ `cat $PGDATA/PG_VERSION` != '7.3' ] .radek. ---(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: [ADMIN] list schema
We should have one, but the only solution now is: test= select * from pg_namespace; --- Jie Liang wrote: Bruce, I wonder which command can show all schemas in 7.3? I mean: \dt -- show all tables \l -- show all databases ?? -- show all schemas Thanks. Jie Liang Software Engineer St. Bernard Software 16882 W. Bernardo Dr. San Diego, CA 92127 Tel: 858-524-2134 Fax:858-676-2228 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- 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 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: [ADMIN] list schema
Also, on TODO I see: * Add schema, cast, and conversion backslash commands to psql so we know we need them. --- Jie Liang wrote: Bruce, I wonder which command can show all schemas in 7.3? I mean: \dt -- show all tables \l -- show all databases ?? -- show all schemas Thanks. Jie Liang Software Engineer St. Bernard Software 16882 W. Bernardo Dr. San Diego, CA 92127 Tel: 858-524-2134 Fax:858-676-2228 [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 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: [ADMIN] fresh 7.3-1 SRPMS bug
On Thursday 05 December 2002 14:23, Radoslaw Stachowiak wrote: postgresql.init script contains line: if [ `cat $PGDATA/PG_VERSION` != '7.2' ] while it of course should be: if [ `cat $PGDATA/PG_VERSION` != '7.3' ] Argh. It'll start ONCE, but not TWICE. Pretend you didn't see the 7.3-1PGDG RPMset or if you did, and downloaded it, make that change to /etc/rc.d/init.d/postgresql. A 7.3-2PGDG RPMset is building nowwill upload as soon as it's done (I'm at work; the T1 is cranked today) -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] list schema
Bruce Momjian [EMAIL PROTECTED] writes: Also, on TODO I see: * Add schema, cast, and conversion backslash commands to psql so we know we need them. I was going to add one for schemas, but \ds and \dS are already taken, and there was no good proposal for what to use instead. Any thoughts? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] list schema
--On Thursday, December 05, 2002 15:33:01 -0500 Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: Also, on TODO I see: * Add schema, cast, and conversion backslash commands to psql so we know we need them. I was going to add one for schemas, but \ds and \dS are already taken, and there was no good proposal for what to use instead. Any thoughts? \dn for NameSpace? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] Postgres 7.3 and phpPgAdmin
We just upgraded our development server (Redhat 7.1) to PostgreSQL 7.3 (last night) and have been using phpPgAdmin for various functions. It seems that phpPgAdmin-2.4.2 (Released in July) doesn't work completely with PostgreSQL 7.3. Our big problem is that trying to do an SQL dump of a database generates a page of errors indicating that it can't find a number of system tables. I would guess that 7.3 may have changed things just a bit :-). So, does anyone know if there are patches/new versions of phpPgAdmin running around anywhere? Most of our users are running Windows so we need a web based tool for this. Thanks, much... Cheers! -- Robert M. Meyer Sr. Network Administrator INSTALLS inc 14 Lafayette Sq, Ste 410 Buffalo, NY 14203-1904 (716)332-1451 04 (716)332-1451 ---(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: [ADMIN] list schema
How about \D for Domains...
BTW, because many people ask how to do the equivalant in SQL of a \
command,
why not be done with the \ commands which are criptic anyway and instead
create global stored procedures like:
sp_list_databases()
sp_list_schemas()
sp_list_{whatever}()
sp_enable_{this}()
sp_disable_{that}()
JLL
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Also, on TODO I see:
* Add schema, cast, and conversion backslash commands to psql
so we know we need them.
I was going to add one for schemas, but \ds and \dS are already taken,
and there was no good proposal for what to use instead. Any thoughts?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] list schema
I believe \dn is better. Jie Liang Software Engineer St. Bernard Software 16882 W. Bernardo Dr. San Diego, CA 92127 Tel: 858-524-2134 Fax:858-676-2228 [EMAIL PROTECTED] -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 12:36 PM To: Tom Lane Cc: Jie Liang; [EMAIL PROTECTED] Subject: Re: [ADMIN] list schema Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Also, on TODO I see: * Add schema, cast, and conversion backslash commands to psql so we know we need them. I was going to add one for schemas, but \ds and \dS are already taken, and there was no good proposal for what to use instead. Any thoughts? I guess we have to go with the second letter, c, or use n for namespaces. -- 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 4: Don't 'kill -9' the postmaster
[ADMIN] why schema name is same as username behaves different then others
Note:
test=# super user postgres
test= regular user robot
### comments
test=#create schema t AUTHORIZATION robot;
CREATE SCHEMA
test= select * from pg_namespace ;
nspname | nspowner | nspacl
+--+
pg_catalog |1 | {=U}
pg_toast |1 | {=}
public |1 | {=UC}
pg_temp_1 |1 |
t | 1045 |
test= create table foo(test text);
CREATE TABLE
test= \dt
List of relations
Schema | Name | Type | Owner
+--+---+---
public | foo | table | robot
test= create table t.foo(test text);
CREATE TABLE
test= \dt
List of relations
Schema | Name | Type | Owner
+--+---+---
public | foo | table | robot
(1 row)
I expect to see something like:
List of relations
Schema | Name | Type | Owner
+--+---+---
public | foo | table | robot
t | foo | table | robot
test= insert into foo values('');
INSERT 19868125 1
test= insert into t.foo values('s');
INSERT 19868126 1
test= select * from foo;
test
--
(1 row)
test=# create schema robot AUTHORIZATION robot;
CREATE SCHEMA
test= select * from pg_namespace ;
nspname | nspowner | nspacl
+--+
pg_catalog |1 | {=U}
pg_toast |1 | {=}
public |1 | {=UC}
pg_temp_1 |1 |
t | 1045 |
robot | 1045 |
(6 rows)
test= create table robot.foo(test text);
CREATE TABLE
test= \dt
List of relations
Schema | Name | Type | Owner
+--+---+---
robot | foo | table | robot
(1 row)
I expect to see something like:
List of relations
Schema | Name | Type | Owner
+--+---+---
public | foo | table | robot
t | foo | table | robot
robot | foo | table | robot
why it's not?
test= insert into robot.foo values('x');
INSERT 19868173 1
test= select * from foo;
test
---
x
(1 row)
why schema t,robot behave different???
it seems if schema name is same as username then
it will shadow the objects in the public schema that have same name.
I haven't documentation address this yet.
Jie Liang
Software Engineer
St. Bernard Software
16882 W. Bernardo Dr.
San Diego, CA 92127
Tel: 858-524-2134
Fax:858-676-2228
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] why schema name is same as username behaves different then others
Jie Liang [EMAIL PROTECTED] writes: I expect to see something like: You're neglecting the effects of search path. \dt only shows what's visible in your search path. (IIRC, \dt *.* is the easiest way to see all tables regardless of search path.) it seems if schema name is same as username then it will shadow the objects in the public schema that have same name. That's what it's supposed to do, and yes this is documented, and yes you can change it if you don't like it. Read about search_path. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] list schema
Tom Lane writes: I was going to add one for schemas, but \ds and \dS are already taken, and there was no good proposal for what to use instead. Any thoughts? \dschema Pretty soon we'll have too many things for 'c' as well, so maybe we should go with full words. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] why schema name is same as username behaves different
Jie Liang wrote: I expect to see something like: List of relations Schema | Name | Type | Owner +--+---+--- public | foo | table | robot t | foo | table | robot That's because schema t is not in your search path. By default, search path is: regression=# show search_path ; search_path -- $user,public (1 row) So you are not seeing the table in schema foo. If you do: regression=# create user robot; CREATE USER regression=# create schema t AUTHORIZATION robot; CREATE SCHEMA regression=# drop table foo; DROP TABLE regression=# create table foo(test text); CREATE TABLE regression=# create table t.foo(test text); CREATE TABLE regression=# \dt List of relations Schema | Name | Type | Owner ++---+-- public | foo| table | postgres public | table1 | table | postgres public | table2 | table | postgres (3 rows) regression=# set search_path to 't','public'; SET regression=# \dt List of relations Schema | Name | Type | Owner ++---+-- public | table1 | table | postgres public | table2 | table | postgres t | foo| table | postgres (3 rows) The $user in the default search path allows user robot to automatically find objects in schema robot first. You can change the default search path for the installation in postgresql.conf, or you can change in via ALTER DATABASE or ALTER USER to be effective in just one database or for one user respectively. HTH, Joe ---(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: [ADMIN] why schema name is same as username behaves different then ot
Got it. Thank you. Jie -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 2:58 PM To: Jie Liang Cc: 'Bruce Momjian'; [EMAIL PROTECTED] Subject: Re: why schema name is same as username behaves different then others Jie Liang [EMAIL PROTECTED] writes: I expect to see something like: You're neglecting the effects of search path. \dt only shows what's visible in your search path. (IIRC, \dt *.* is the easiest way to see all tables regardless of search path.) it seems if schema name is same as username then it will shadow the objects in the public schema that have same name. That's what it's supposed to do, and yes this is documented, and yes you can change it if you don't like it. Read about search_path. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] why schema name is same as username behaves different then ot
But why shadow happens only schema name is SAME as username?? Jie -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 2:58 PM To: Jie Liang Cc: 'Bruce Momjian'; [EMAIL PROTECTED] Subject: Re: why schema name is same as username behaves different then others Jie Liang [EMAIL PROTECTED] writes: I expect to see something like: You're neglecting the effects of search path. \dt only shows what's visible in your search path. (IIRC, \dt *.* is the easiest way to see all tables regardless of search path.) it seems if schema name is same as username then it will shadow the objects in the public schema that have same name. That's what it's supposed to do, and yes this is documented, and yes you can change it if you don't like it. Read about search_path. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] list schema
On Thursday 05 December 2002 23:21, Peter Eisentraut wrote: Tom Lane writes: I was going to add one for schemas, but \ds and \dS are already taken, and there was no good proposal for what to use instead. Any thoughts? \dschema Pretty soon we'll have too many things for 'c' as well, so maybe we should go with full words. I think this sounds like a good idea. as they would be much easier to remember if its a full word. You can always leave the 1 leave the 1 letter abbrevations in anyway and have both! I also notice that on the todo list is a job to turn all these into views. It might be worth doing that (should not be too difficult I think) plus they could then be used from other client programs. Strictly I think this meta information should have its own access methods in the sql standard because currently sql is a broken standard with out this meta infomation. Peter Childs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
