Обсуждение: Changing pg_type records

Поиск

Список

Период

Сортировка

Changing pg_type records

От

"Rob Richardson"

Дата:

19 марта 2009 г., 14:44:56

Greetings!

I just discovered the existence of a couple of functions I don't understand in one customer's PostgreSQL database:

-- Function: c_mode()

-- DROP FUNCTION c_mode();

CREATE OR REPLACE FUNCTION c_mode()
RETURNS text AS
$BODY$ UPDATE pg_type SET typoutput='c_textout' WHERE typname='SET';

UPDATE pg_type SET typoutput='c_varcharout' WHERE typname='bpchar';

UPDATE pg_type SET typoutput='c_textout' WHERE typname='bytea';

UPDATE pg_type SET typoutput='c_charout' WHERE typname='char';

UPDATE pg_type SET typoutput='c_textout' WHERE typname='text';

UPDATE pg_type SET typoutput='c_textout' WHERE typname='unknown';

UPDATE pg_type SET typoutput='c_varcharout' WHERE typname='varchar';

select 'c_mode'::text;$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION c_mode() OWNER TO postgres;

-- Function: pg_mode()

-- DROP FUNCTION pg_mode();

CREATE OR REPLACE FUNCTION pg_mode()
RETURNS text AS
$BODY$ UPDATE pg_type SET typoutput='textout' WHERE typname='SET';

UPDATE pg_type SET typoutput='varcharout' WHERE typname='bpchar';

UPDATE pg_type SET typoutput='textout' WHERE typname='bytea';

UPDATE pg_type SET typoutput='charout' WHERE typname='char';

UPDATE pg_type SET typoutput='textout' WHERE typname='text';

UPDATE pg_type SET typoutput='textout' WHERE typname='unknown';

UPDATE pg_type SET typoutput='varcharout' WHERE typname='varchar';

select 'pg_mode'::text;$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION pg_mode() OWNER TO postgres;

This customer has demanded extensive modifications to their database and our application. I think these functions are used in encryption, since there are several other non-standard functions in this database that are related to encryption.

Our lead developer is in another state, and is naturally very uncommunicative. When I asked him about the c_mode() function and expressed concern that changing pg_type records might not be a good idea, his reply was:

"Ignore it, it is a database contrib routine for string I/O. It doesn't change the database properties."

Are these functions really as innocuous as he claims? It seems to me that between the time c_mode() is called and the time that pg_mode() is called, any other database access is likely to be confused.

Thank you very much.

RobR

Re: Changing pg_type records

От

Tom Lane

Дата:

19 марта 2009 г., 20:00:37

"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> I just discovered the existence of a couple of functions I don't
> understand in one customer's PostgreSQL database:

What do the substitute output functions do differently?

> UPDATE pg_type SET typoutput='c_textout'    WHERE typname='SET';

This one has been a no-op since 7.4, because there is no longer any such
row in pg_type; and I'm pretty sure the row was vestigial long before that.
If the underlying code is old enough that it was designed when changing
that row did something interesting, it certainly all needs a fresh look
anyway...

> Are these functions really as innocuous as he claims?  It seems to me
> that between the time c_mode() is called and the time that pg_mode() is
> called, any other database access is likely to be confused.

Yes, you definitely are taking big risks there.  Aside from the question
of how data appears to the client, this could bollix the behavior of
most PL languages too.

            regards, tom lane

Is there a meaningful benchmark?

От

"Will Rutherdale (rutherw)"

Дата:

19 марта 2009 г., 20:32:08

Hi.  I'm writing up a database comparison paper in my department at
work, with Postgres being a major candidate.  I have been attempting to
research various issues and provide a meaningful comparison.

One issue I would like to give some kind of information on is
comparative performance.  When I look for such things in Google or
Wikipedia or the pgsql email archives, it's hard to find anything
reasonably definitive.  I've found isolated claims and anecdotes here
and there, and a fellow on the list here who attempted to do a
comparison between Postgres, MySQL, and Oracle but gave it up for now.

Some of the claims I've seen said that in some cases MySQL with MyISAM
ran 2x faster than Postgres, but that may have been for a special case
with only read access to the database;  whereas another one claimed that
MySQL with InnoDB was slower than Postgres.  Other people commented that
it depends on how you tune the databases.

Maybe there's nothing definitive out there.  However I'd like to get a
ballpark idea of how some databases compare, using some kind of average
case schema and application, in terms of transactions per second, on a
common hardware platform.  I would like to be able to point to a
reasonable reference, rather than engaging in handwaving myself.

Does anyone know where I could look?

-Will

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Changing pg_type records

Вложения