Thread: general questions

Hi All;

I recently ran into the following, any thoughts?

Thanks in advance...

1) \d and schema's
- I setup 2 schema's (sch_a and sch_b)
- I added both schema's to my search_path
- I created 2 tables:  sch_a.test_tab and sch_b.test_tab

If I do a \d with no parameters I only see the first test_tab table
based on the order of my search_path.
I get that any queries will use the first found table if I don't specify
the schemaname but
if I'm looking for a full list (i.e. \d with no parameters) I would
think I should get a full list back

Is this intentional?


2) SET work_mem = x
It seems that any user can run set work_mem = x in a session. While this
is keen for
folks who know what they are doing, it may not be so keen for folks who
tend to do
foolish things, especially if a team has several of these types of folks
on board
i.e. I can Imagine 5 devs all setting work_mem to 5GB each and running
giant runaway
queries all on a dev server with 8GB of RAM.

Is there a way to restrict this?

3) Can I force unaligned mode AND no wrap for psql output?

4) Is there a way to know for sure ifa sql file was run in single
transaction mode (after the fact), i.e. something in the logs?

5) Is there a query that will show me the actual prepared SQL text for a
prepared query?

CS DBA <cs_dba@consistentstate.com> writes:
> 1) \d and schema's
> - I setup 2 schema's (sch_a and sch_b)
> - I added both schema's to my search_path
> - I created 2 tables:  sch_a.test_tab and sch_b.test_tab

> If I do a \d with no parameters I only see the first test_tab table
> based on the order of my search_path.
> I get that any queries will use the first found table if I don't specify
> the schemaname but
> if I'm looking for a full list (i.e. \d with no parameters) I would
> think I should get a full list back

> Is this intentional?

Yes.  If you want to see stuff that's invisible in your current search
path, use "\d *.*".  That's even documented somewhere ...

> 2) SET work_mem = x
> It seems that any user can run set work_mem = x in a session.

Yup.  If a user can issue arbitrary SQL, they can drive your server into
the ground with or without that, so I see little point in restricting it.
(Indeed, restricting it could be counterproductive, since too *small*
a value can be just as bad for performance as too large.)

> 3) Can I force unaligned mode AND no wrap for psql output?

[ shrug ... ]  Dunno, read the manual.

> 4) Is there a way to know for sure ifa sql file was run in single
> transaction mode (after the fact), i.e. something in the logs?

If you're logging individual statements, there will be BEGIN and
COMMIT logged.  If you're not, I doubt the log will even tell you
a sql file was run, let alone such details.

> 5) Is there a query that will show me the actual prepared SQL text for a
> prepared query?

select * from pg_prepared_statements

            regards, tom lane