[pgadmin-support] ALTER TABLE ALTER COLUMN in view
Hi all,
I have a scenario like follow (script generated with pg_dump) :
CREATE TABLE teste (
id integer NOT NULL,
ds character varying(20),
n2 character varying(10)
);
CREATE SEQUENCE teste_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE teste ALTER COLUMN id SET DEFAULT nextval('teste_id_seq'::regclass);
ALTER TABLE ONLY teste
ADD CONSTRAINT pk_teste PRIMARY KEY (id);
ALTER SEQUENCE teste_id_seq OWNED BY teste.id;
CREATE VIEW vteste AS
SELECT teste.id, teste.ds, teste.n2 FROM teste;
ALTER TABLE vteste ALTER COLUMN id SET DEFAULT
nextval('teste_id_seq'::regclass);
Well this last line make a change on a view column and it works.
I do it because when inserting a row in the view vteste, I got a message error
like "teste.id does not accept NULL values"
Searching in the internet, I found a message where Tom Lane suggest to do this.
Well, I don´t found anything in Postgresql manual saying that we can do it in
views, but I don´t found anything saying that we don´t must do it too.
Since pg_dump generates the script with this definition, I think that they
must be supported by PostgreSQL
The only thing that I have a objection is because this don´t appear in pgadmin
views definition. And we don´t mind that this "ALTER COLUMN" exists in the
Database structure.
I want to request/suggest that views script generates a complete script in the
sqlpane like when select a table (with index,triggers creation sql scripts) or
with at least this DDL, since we can´t see it anywhere.
May we have a icon for this alter columns in the view definition tree too ?
I´m using pgAdmin 1.6.3 in a winXP pro SP2 with Postgresql 8.2.4
Thanks in Advance
Luiz
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[pgadmin-support] show application_name
Olá pessoal, gostaria de saber como é possível saber o nome da aplicação que está rodando através da query do postgreSQL Estou criado uma função em PL/SQL da seguinte forma: CREATE OR REPLACE FUNCTION audit() RETURNS trigger AS $BODY$ declare sApplication text; begin execute 'show application_name' into sApplication; end; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION audit() OWNER TO solution; O objetivo é saber se a alteração de um registro foi feito pelo sistema ou diretamente no postgreSQL obrigado desde já att, Luiz Eduardo A. Londero -- View this message in context: http://postgresql.1045698.n5.nabble.com/show-application-name-tp5647490p5647490.html Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com. -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
[pgadmin-support] PgAdmin crash on windows 8.1
Hi, PgAdmin 3 stops at the application launch in Windows 8.1 I think it's related to some windows update because pgadmin was working fine a month or two before I can not say exactly when this started to happen because I do not using pgadmin so intensely lately. I was using pgAdmin 1:18, I tried to repair the installation without success. Then updated to version 1.20 but the problem persists. The windows application log says: Nome do aplicativo com falha: pgadmin3.exe, versão: 1.20.0.99, carimbo de data/hora: 0x54940b5e Nome do módulo com falha: wxbase28u_vc_custom.dll, versão: 2.8.12.0, carimbo de data/hora: 0x5359f80e Código de exceção: 0xc005 Deslocamento da falha: 0x000534f7 ID do processo com falha: 0x1644 Hora de início do aplicativo com falha: 0x01d03b22f3d9dc4d Caminho do aplicativo com falha: C:\w\pgAdmin III\1.20\pgadmin3.exe Caminho do módulo com falha: C:\w\pgAdmin III\1.20\wxbase28u_vc_custom.dll ID do Relatório: 3e78e919-a716-11e4-bf69-a41f72f8f49e Nome completo do pacote com falha: ID do aplicativo relativo ao pacote com falha: Can anyone help me? Thanks in advance
[pgadmin-support] Function name string bug pgadmin 1.4.1
Hi all, I want to report a bug in pgAdmin v 1.4.1 When we try to change some propertie in trigger function in the tree Databases |- |- Schemas |- |- Tables |- |- Triggers |- |- in the properties window they generate a wrong . string like : CREATE OR REPLACE FUNCTION .() When i use the properties window trough Databases |- |- Schemas |- |- Trigger Functions |- It works write generating: CREATE OR REPLACE FUNCTION .() Thanks in Advance. PS. Thanks by create this wonderful tool, it´s really a good job. Sorry by the poor english. Luiz Matsumura
Re: [pgadmin-support] OK button on restore
I agree with Pierce, instead of OK buttom we can have another message like "start restore" or just "restore". I don't remember how much times inadvertently press OK buttom to close this screen. (Yeah, I was thinking that this occur only with me :P ) This is a little change, but will help a lot. TIA. Pierce Tyler wrote: That makes sense. But it still might be helpful to add an additional safeguard (like a pop-up message, for example). I've seen cases where the log returns only one error--something insignificant--and the user decides they can live with it. So they click OK, thinking they're exiting the screen, moving on. But instead they may have inadvertently corrupted their database. Dave Page wrote: Pierce Tyler wrote: This may be more of a feature request/suggestion than a bug. But here's the issue: After finishing a restore using the Windows version (tested in 1.6.3, but was also present in 1.4.x), if you click the "OK" button when you're done, the restore process runs a 2d time--which is not desirable. I've used the program enough to know I should select "CANCEL" instead. But the distinction between OK and CANCEL (or "DONE", which I believe I've seen in some versions) is not totally clear. The bottom line is it's far too easy for a user to click OK thinking they're doing the right thing, when in fact it's not the right thing to do. If the restore doesn't return a success code the button stays at OK so you can tweak the options and try again. If it does return a success code (zero), the button changes to Done. The same applies to the backup dialogues. Regards, Dave -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 1: 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
[pgadmin-support] Problem with Serial Columns
Hi all,
I'm using Postgres 8.2.4 with pgAdmin 1.6.3 (but this occur with 1.8.0
beta 1 too)
When we create something like
CREATE SCHEMA example;
CREATE TABLE example.teste
(
id serial NOT NULL ,
dsitem character varying(30),
CONSTRAINT pk_teste PRIMARY KEY (id)
);
All works fine, but if we do a backup using pg_dump and restore it,
pgAdmin doesn't recognize id as a serial anymore, presenting now the
table definition bellow
CREATE TABLE example.teste
(
id integer NOT NULL DEFAULT nextval('teste_id_seq'::regclass),
dsitem character varying(30),
CONSTRAINT pk_teste PRIMARY KEY (id)
);
This isn't a big problem, but make the things confusing, since at first
impression, we can think that the sequence teste_id_seq
wasn't dependent of column teste.id. But if we drop the table, the
sequence is dropped too (as we expect in a serial column).
I research pg_dump script and see that pg_dump recreate the table with
the commands bellow
CREATE SCHEMA example;
SET search_path = example, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE teste (
id integer NOT NULL,
dsitem character varying(30)
);
CREATE SEQUENCE teste_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER SEQUENCE teste_id_seq OWNED BY teste.id;
ALTER TABLE teste ALTER COLUMN id SET DEFAULT
nextval('teste_id_seq'::regclass);
ALTER TABLE ONLY teste
ADD CONSTRAINT pk_teste PRIMARY KEY (id);
Well, I found in pgColumn.cpp that default string expected is
"nextval('example.teste_id_seq'::regclass)", but pg_dump set this value
to "nextval('teste_id_seq'::regclass)".
If we change the default value of column to
"nextval('example.teste_id_seq'::regclass)", then all work's fine again.
In pg_dump, the adstr column that contains the default value for the
column is retrieved using the function
pg_catalog.pg_get_expr(adbin,adrelid), that will return the string
"nextval('example.teste_id_seq'::regclass)",
But ONLY IF the schema ISN'T in the search_path. I suppose that
pgadmin don't alter the search_path, so this can resolve the problem for
a while.
The atacched diff file modify this (reference is the source of 1.6.3),
but I don't have sufficient skill to compile the source and I'm not a
C/C++ programmer to do a better change.
I think that a better test to serial columns, may be to verify primarily
if there are a dependence between the column and the sequence, and then
verify if the default value of the column is a nextval of the this sequence.
PS.: With the schema "public" all works fine
--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.
*** pgColumn.cpp2007-03-23 13:11:43.0 -0300
--- pgColumn2.cpp 2007-07-25 15:33:39.0 -0300
***
*** 270,276
systemRestriction = wxT("\n AND attnum > 0");
wxString sql=
! wxT("SELECT att.*, def.*, CASE WHEN attndims > 0 THEN 1 ELSE 0 END AS
isarray, format_type(ty.oid,NULL) AS typname, tn.nspname as typnspname,
et.typname as elemtypname,\n")
wxT(" cl.relname, na.nspname, att.attstattarget, description,
cs.relname AS sername, ns.nspname AS serschema,\n")
wxT(" (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname)
> 1 AS isdup, indkey");
--- 270,276
systemRestriction = wxT("\n AND attnum > 0");
wxString sql=
! wxT("SELECT att.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS
adsrc, CASE WHEN attndims > 0 THEN 1 ELSE 0 END AS isarray,
format_type(ty.oid,NULL) AS typname, tn.nspname as typnspname, et.typname as
elemtypname,\n")
wxT(" cl.relname, na.nspname, att.attstattarget, description,
cs.relname AS sername, ns.nspname AS serschema,\n")
wxT(" (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname)
> 1 AS isdup, indkey");
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[pgadmin-support] PgAdmin 1.8.0 beta 3 bug: Open file in Query Tool
Dave Page wrote: Not really - it's generated from all the other bits of data. I updated the .exe at http://developer.pgadmin.org/~dpage/pgadmin3.zip; try it out and see what you think. /D Hi, I download this snapshot, and detect a problem with query tool ( win XP pro) The file open option is not working for me: I select the file but nothing is loaded from the file. I tested with the original 1.8.0 beta3 pgadmin.exe and it not load files too. Version 1.6.3 works fine. TIA -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [pgadmin-support] PgAdmin 1.8.0 beta 3 bug: Open file in Query Tool
Hi, I download this snapshot, and detect a problem with query tool ( win XP pro) The file open option is not working for me: I select the file but nothing is loaded from the file. I tested with the original 1.8.0 beta3 pgadmin.exe and it not load files too. Version 1.6.3 works fine. TIA Hi, I'm again... I investigate more, and this ocurr only with files with files with ansi files with accentuated characters ( like "çãé..." ). If I disable the option on the preferences: "Read and write Unicode UTF-8 files", then the file is loaded. If I edit a file with this option enabled, then this characters is write in Unicode (I suppose) and now the file is correctly loaded and displayed. Disabling this option, now the file is loaded, but the characters isn't translated. But in 1.6.3 both files are loaded with "Read and write Unicode UTF-8 files" enabled and translated to corresponding characters. This is a new behavior or a bug ? -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [pgadmin-support] PgAdmin 1.8.0 beta 3 bug: Open file in Query Tool
Hiroshi Saito wrote: Hi, I'm again... I investigate more, and this ocurr only with files with files with ansi files with accentuated characters ( like "çãé..." ). If I disable the option on the preferences: "Read and write Unicode UTF-8 files", then the file is loaded. If I edit a file with this option enabled, then this characters is write in Unicode (I suppose) and now the file is correctly loaded and displayed. Yes, The option always desires reading and writing by UTF-8. Disabling this option, now the file is loaded, but the characters isn't translated. Umm, As for Japanes, The beginning serves as SJIS localized correctly. Then, Each of SJIS and UTF-8 is correctly expressed for reading. If possible, please send the file containing the code of your problem.. Hi Hiroshi, Thanks for reply. Sample file attached. Best Regards -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. -- Test File for pgAdmin 1.8.0 -- With the line bellow, the file don't load with the option "Read and write Unicode UTF-8 files" enabled -- Criação do schema teste -- CREATE SCHEMA teste; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgadmin-support] PgAdmin 1.8.0 beta 3 bug: Open file in Query Tool
Hi. Hiroshi Saito wrote: Umm, UTF8 was not able to be found from the received file.?_? Can my test code be used? http://winpg.jp/~saito/pgAdmin/SQLTEST_ANSI_UTF8.zip However, ANSI file is Shift_jis. please use the UTF8.sql. Regards, Hiroshi Saito Yes, your code is perfect. The problem occur with ANSI files. When the file is UTF8, it works correctly with or without "Read and write Unicode UTF-8 files" option enabled Regards -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 1: 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: [pgadmin-support] PgAdmin 1.8.0 beta 3 bug: Open file in Query Tool
Hiroshi Saito wrote: Ah yes, Reading is generous a little. However, It is "Read and write" option Do you desire a function which only writing limited? In the distant past, It had classified to the timing of reading and writing before. It is the evolved action now. Therefore, I think the method which cleared confusion by now. Although it may be necessary to still evolve. Regards, Hiroshi Saito Hi Saito san, IMHO, I think that a better approach is to PgAdmin respect the original file format when saving a loaded file. When the file is ANSI, no translations for characters (even when we have possible Unicode encoded characters) When the file is UTF8, load and save as UTF8 And put an option in the "Save as" window to select the file format to save (ANSI or UTF8) On the options windows we can have an option to default file format. Regards, Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[pgadmin-support] PgAdmin3 1.8.0 Beta 3 r 6606 bug
Hi,
I downloaded the last snapshot on Dave page (svn rev 6606).
This version don't work with postgres 8.1 and 8.0.
I think that error is caused by a modification in pgDatabase.c
I try to find an equivalent query but I not sure if this is correct,
please see if this will work fine.
Hope this can help.
Regards
--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.
Index: H:/svn/pgadmin3/pgadmin3/pgadmin/schema/pgDatabase.cpp
===
--- H:/svn/pgadmin3/pgadmin3/pgadmin/schema/pgDatabase.cpp (revision 6607)
+++ H:/svn/pgadmin3/pgadmin3/pgadmin/schema/pgDatabase.cpp (working copy)
@@ -492,8 +492,8 @@
wxT("SELECT db.oid, datname, spcname, datallowconn, datconfig,
datacl, ")
wxT("pg_encoding_to_char(encoding) AS serverencoding,
pg_get_userbyid(datdba) AS datowner,")
wxT("has_database_privilege(db.oid, 'CREATE') as cancreate, \n")
- wxT("(select setting AS default_tablespace from
pg_show_all_settings() x(name text, setting text, unit text, category text,
short_desc text, extra_desc text, context text, vartype text, source text,
min_val text, max_val text) where name = 'default_tablespace') AS
default_tablespace\n")
- wxT(" FROM pg_database db\n")
+ wxT("select current_setting('default_tablespace') AS
default_tablespace\n")
+ wxT(" FROM pg_database db\n")
wxT(" LEFT OUTER JOIN pg_tablespace ta ON
db.dattablespace=ta.OID\n")
+ restr +
wxT(" ORDER BY datname"));
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [pgadmin-support] PgAdmin3 1.8.0 Beta 3 r 6606 bug
Dave Page wrote: set_config() current_setting() pg_show_all_settings() which couldn't be more different in naming styles if they tried! Yes, and I as we say here in Brazil: "Paguei um mico" I don't know how to translate, but is something like "to be with shame" (google translation) I search by 1 hour for this function and don't find nothing. Then I send a suggestion to pg list to include a get_config() function and Alvaro Herrera show me that's this already exists as current_setting() function. Thanks to Alvaro. -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[pgadmin-support] 1.8.0 Beta 4 Bug: Crash on create new database
PgAdmin3 1.8.0 beta 4 OS: winXP When try to create a new database, pgAdmin crash. (via context menu or menu edit > New Object > New Database ) Unable to see database properties too (crash) (via context menu or menu edit > properties) TIA -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[pgadmin-support] Bug in Query Tool pgAdmin III 1.10.0 - Query Menu
Hi, Just a little bug. In the query tool screen, each time we switch from "Graphical Query Builder" pane to "SQL Editor" pane a new item is added to Menu Query : "Generate SQL from Graphical query builder model". If we switch 10 times then 10 entries is added to menu. Environment: Windows XP pro sp3 PgAdmin 1.10.0 Best Regards, -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
[pgadmin-support] Opening Query tool exausting windows memory - v 1.10.0
Just for recording and help someone if this occurs PgAdmin 3 v 1.10.0 Windows XP pro Sp3 I was unable to open Query tool window in pgAdmin 1.10.0 , each time I try to open, cpu usage go to 100 % and memory usage jump from around 400 MB to more than 1.5 GB in seconds. The solution I found : Open Query Tool window in pgAdmin 1.8.4 then restore default settings via menu View -> Default View Then Query tool windows now open normaly in pgAdmin 1.10.0 Best Regards, -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
[pgadmin-support] Feature request - View Filtered rows
Hi all, First at all, thanks for this great tool ! I have some suggestions for improve this great tool: Add options to select order and limit of rows on filtered view Best Regards -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
[pgadmin-support] Volumetrics Data
Hi, I am looking for information about how to estimate volumetric data for a Postgre database. I have to build an application that calculates the volumetrics data based on a data model. Does anybody can help me? I don't know if this mailing list is the correct place to this question. If no, where is? Att, Luiz Cesar
[pgadmin-support] unsubscribe
[pgadmin-support] unsubscribe
unsubscribe
[pgadmin-support] Cannot visualize table
I cannot visualize the table:
CREATE TABLE public."Experimento"
(
"id_Experimento" int8 NOT NULL DEFAULT
nextval('"ExperimentoSequencia"'::text),
"id_Configuracao" int8 NOT NULL,
desligar bool DEFAULT false,
"atualizacaoCliente" oid NOT NULL,
"programaCliente" oid NOT NULL,
"versaoCliente" float8 NOT NULL,
prioridade float8 DEFAULT 1,
CONSTRAINT "Evolucoes_pkey" PRIMARY KEY ("id_Experimento"),
CONSTRAINT "FK_EvolucaoConfiguracao" FOREIGN KEY ("id_Configuracao")
REFERENCES public."Configuracao" (id_configuracao) ON UPDATE NO ACTION
ON DELETE NO ACTION
) WITH OIDS;
I click on the button view data and it shows nothing. Not even the colluns.
I´m running the last pgadmin3 snapshot.
WinXP
PG 7.3.2.
Luiz Angelo Daros de Luca
Federal University of Santa Catarina
Florianopolis - SC - Brazil
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[pgadmin-support] [BUG] Wrong colors on SQL table result
Hello, I'm using pgadmin3 1.14.3 on OpenSUSE 12.3. I'm facing problems with pgadmin3 color on SQL query result table when using "OpenSUSE Dark theme" on KDE. pgadmin3 uses: non selected cell background color = View Background (correct) non selected cell text color = Button text (wrong, should be View Text) selected cell background color = Selection Background (correct) selected cell text color = Window Text (wrong, should be Selection Text) In the SQL query result table, my View Background and Button text are both white. So, this way, I get blank cells. For light themes, generally Button text and view text are the same. There is other non-critical color problems with tabs, object browser tree that are easly visible when a dark window backound color scheme is used. Also, when I change the colorscheme, some parts of pgadmin updates its colors while others keep the startup color. Maybe there is some event that needs to be treated. Regards, --- Luiz Angelo Daros de Luca, Me. luizl...@gmail.com
Re: [pgadmin-support] [BUG] Wrong colors on SQL table result
Hello Guillaume, Thanks for your answer. However, I just tested 1.16.1. The problem is still present. Regards, --- Luiz Angelo Daros de Luca, Me. luizl...@gmail.com 2013/6/19 Guillaume Lelarge > On Wed, 2013-06-19 at 16:07 -0300, Luiz Angelo Daros de Luca wrote: > > Hello, > > > > I'm using pgadmin3 1.14.3 on OpenSUSE 12.3. I'm facing problems with > > pgadmin3 color on SQL query result table when using "OpenSUSE Dark theme" > > on KDE. pgadmin3 uses: > > > > non selected cell background color = View Background (correct) > > non selected cell text color = Button text (wrong, should be View Text) > > selected cell background color = Selection Background (correct) > > selected cell text color = Window Text (wrong, should be Selection Text) > > > > In the SQL query result table, my View Background and Button text are > both > > white. So, this way, I get blank cells. > > For light themes, generally Button text and view text are the same. > > > > There is other non-critical color problems with tabs, object browser tree > > that are easly visible when a dark window backound color scheme is used. > > > > Also, when I change the colorscheme, some parts of pgadmin updates its > > colors while others keep the startup color. Maybe there is some event > that > > needs to be treated. > > > > 1.16 got some fixes around this, so you should try this release. > > > -- > Guillaume > http://blog.guillaume.lelarge.info > http://www.dalibo.com > >
